Problems with stored procedure in SQL Server

Question

I would like to retrieve total rows and also all values through a stored procedure in SQL Server. I have something like below. However, it mentioned of it is not contained in either an aggregate function or the GROUP BY clause. Appreciate for any reply. Thanks.

ALTER PROCEDURE abc.testing
    @facility varchar(50), 
    @date datetime
AS

SELECT count(*), * 
FROM ss_table 
WHERE _start = @date 
      And fid IN (SELECT id 
                  FROM ss_facilities 
                  WHERE name = @facility)

Igor Borisenko · Accepted Answer · 2012-02-01 01:13:46Z

1

Try this

SELECT SS.cnt, ss_table.* 
FROM ss_table
     cross apply (SELECT count(*) cnt
                  FROM ss_table 
                  WHERE _start = @date 
                        And fid IN (SELECT id 
                                    FROM ss_facilities 
                                    WHERE name = @facility)                
                 )  ss 
WHERE _start = @date 
      And fid IN (SELECT id 
                  FROM ss_facilities 
                  WHERE name = @facility)

answered Feb 1, 2012 at 1:13

Igor Borisenko

3,8663 gold badges38 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bill Martin · Accepted Answer · 2012-02-01 01:24:08Z

1

You need a group clause when using an aggregate function like count. If ss_table has an id column, do the count function on it.

It would work like this:

SELECT count(id), <explicitly define column names except the id here> 
FROM ss_table 
WHERE _start = @date 
And fid IN (SELECT id FROM ss_facilities WHERE name = @facility)
group by <enter every column except the id from ss_table here>

Should work for you.

answered Feb 1, 2012 at 1:24

Bill Martin

4,96310 gold badges57 silver badges87 bronze badges

Comments

bob · Accepted Answer · 2012-02-01 01:07:14Z

0

You can't usefully mix and match aggregate queries and non-aggregate queries - split them up into two separate stored procedures.

(this specific error message is being presented because in order to return columns in an aggregate query, every column must either be in the "group by" fields, or have an aggregate function applied to it ("max", "count", etc). In this query, "*" meets neither of these criteria).

answered Feb 1, 2012 at 1:07

bob

3902 silver badges11 bronze badges

1 Comment

bob Over a year ago

If it wasn't all that clear from my answer, the reason it doesn't allow this is that a query which includes a "count(*)" without a "GROUP BY" clase wants to return a single row for the group of all input rows, whereas "select *" wants to return all of the underlying rows. Apart from anything else, the result format for both of these queries is different - the former being a simple count, the latter being the format of the underlying data.

Collectives™ on Stack Overflow

Problems with stored procedure in SQL Server

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related