SELECT number of groups resulted from a GROUP BY query

Question

I tried the following query to select the number of groups resulting from a GROUP BY query:

SELECT count(*)
FROM (
        SELECT count(*)
        FROM MyTable
        WHERE Col2 = 'x'
        GROUP BY Col1
     )

Unfortunately, this query is not valid: Incorrect syntax near ')'..

Note: I want to get exactly one row (I already found a solution with n times the result, where n = the group count).

just give an alias name for your count(*) in inner query and it should work. — PK20, Nov 26 '15 at 08:50
May the downvoter elaborate please, I would certainly be happy to improve the question if required, thank you. — D.R., Nov 26 '15 at 09:00

score 20 · Answer 1 · answered Nov 26 '15 at 08:50

20

SELECT COUNT(*)
FROM (
    SELECT value = COUNT(*)
    FROM MyTable
    WHERE Col2 = 'x'
    GROUP BY Col1
) е

but i think - you need to try this query -

SELECT COUNT(DISTINCT Col1)
FROM MyTable
WHERE Col2 = 'x'

answered Nov 26 '15 at 08:50

Devart

119,203
23
166
186

1

Was looking for this answer hoping to resort without subqueries and knowing about COUNT DISTINCT eliminating the need of GROUP BY. Unfortunately, such method works only for simplistic grouping, which is not my case involving HAVING over aggregate expression over multiple JOINs. I wonder if there's still a way for a 1-level query over GROUP BY if it cannot be eliminated? – Van Jone Oct 11 '16 at 21:55

jarlh · Accepted Answer · 2015-11-26T08:52:23.587

13

SELECT count(*)
FROM (
        SELECT 1 as dummy
        FROM MyTable
        WHERE Col2 = 'x'
        GROUP BY Col1
     ) dt

No need to count rows in the sub-query, the result will be the same anyway.

edited Nov 26 '15 at 08:52

answered Nov 26 '15 at 08:49

jarlh

42,561
8
45
63

You've been the first, thanks, this works. What's the catch? Why is it so important to define an alias for the column? (Note that it doesn't matter whether I count the rows in the sub-query or not, the alias names are the important stuff). – D.R. Nov 26 '15 at 08:51
I think that's a MS SQL Server thing. Don't know why. Your query is ANSI SQL compliant. – jarlh Nov 26 '15 at 08:53
OK, thanks. Gonna accept your answer asap (9 minutes to go). – D.R. Nov 26 '15 at 08:54
1

Or take a look at Devart's answer below too. – jarlh Nov 26 '15 at 08:55
Why is any text needed after the FROM(...)? `dt` in that case but anything seems to work. – QmlnR2F5 Jul 28 '21 at 12:04
1

@QmlnR2F5, it's a _table alias_, a name for the subquery. Often optional, but some dbms products require it. – jarlh Aug 08 '21 at 13:04

score 3 · Answer 3 · answered Nov 26 '15 at 08:50

3

You have to put names afor columns and tables ...

SELECT count(*) Total
FROM (
        SELECT count(*) Groups
        FROM MyTable
        WHERE Col2 = 'x'
        GROUP BY Col1
     ) a

answered Nov 26 '15 at 08:50

Simone

1,828
1
13
20

score 1 · Answer 4 · edited Jul 01 '23 at 17:25

1

You can also use a CTE in a lot of SQL engines. E.g.

with CTE_table as

(
        SELECT 
              count(*)
        FROM MyTable
        WHERE Col2 = 'x'
        GROUP BY Col1
)

select 
      count(*) 
from  CTE_table

edited Jul 01 '23 at 17:25

abolfazl sadeghi

2,277
2
12
20

answered Sep 07 '22 at 13:13

information_interchange

2,538
6
31
49

1

This is correct and to avoid confusion, I'd recommend tweaking your terminology. That there is a common table expression/CTE, whereas a temporary table is a table named with a leading "#". May be worth revising, just b/c SQL engines treat the 2 very differently – acat Oct 12 '22 at 14:31
Good point, I've revised my answer – information_interchange Oct 13 '22 at 16:02

SELECT number of groups resulted from a GROUP BY query

4 Answers4