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).

Kent

just give an alias name for your count(*) in inner query and it should work. – Wynd

May the downvoter elaborate please, I would certainly be happy to improve the question if required, thank you. – Kent

Answer 1

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.

Sheepfold

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). – Kent

OK, thanks. Gonna accept your answer asap (9 minutes to go). – Kent

Or take a look at Devart's answer below too. – Sheepfold

Why is any text needed after the FROM(...)? dt in that case but anything seems to work. – Pleistocene

@QmlnR2F5, it's a table alias, a name for the subquery. Often optional, but some dbms products require it. – Sheepfold

Answer 2

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'

Veedis

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? – Domiciliary

Answer 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

Recrystallize

Answer 4

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

Southerly

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 – Gastroenteritis

Good point, I've revised my answer – Southerly