CODEWITHSUNDEEP
sqlsql-servert-sql
andarvi
andarvi

Reputation: 150

Aggregate function COUNT not scalar

The COUNT function doesn't result in a scalar as expected:

CREATE TABLE MyTable (Col1 INT, Col2 INT, Col3 INT)
INSERT INTO MyTable VALUES(2,3,9) -- Row 1
INSERT INTO MyTable VALUES(1,5,7) -- Row 2
INSERT INTO MyTable VALUES(2,3,9) -- Row 3
INSERT INTO MyTable VALUES(3,4,9) -- Row 4

SELECT COUNT(*) AS Result
FROM MyTable
WHERE Col3=9
GROUP BY Col1, Col2

I filter out the 3 rows where Col3=9.

In the remaining 3 rows there are two groups:

Group 1 where Col1=2 AND Col2=3 (Row 1 and 3)

Group 2 where Col1=3 AND Col2=4 (Row 4)

Finally I count those two rows.

Therefore, I expect the answer to be a scalar Result = 2 (the two groups where Col3=9).

But I got a non scalar result.

There are other ways to solve the this, so thats not the problem, but where am I thinking wrong?

Upvotes: 0

Views: 167

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

You can use subquery with singe aggregation :

select count(*)
from (select distinct col1, col2
      from mytable
      where col3 = 9
     ) t;

Upvotes: 0

PSK
PSK

Reputation: 17943

Seems like you are looking for the total count of all the groups matching any condition. For this try like the following query.

SELECT COUNT(*) [Count] FROM
(
    SELECT COUNT(*) AS Result
    FROM MyTable
    WHERE Col3=9
    GROUP BY Col1, Col2
)T

SQL Fiddle

Upvotes: 0

Related Questions