Count number of records returned by group by

Solution 1:

You can do both in one query using the OVER clause on another COUNT

select
    count(*) RecordsPerGroup,
    COUNT(*) OVER () AS TotalRecords
from temptable
group by column_1, column_2, column_3, column_4

Solution 2:

The simplest solution is to use a derived table:

Select Count(*)
From    (
        Select ...
        From TempTable
        Group By column_1, column_2, column_3, column_4
        ) As Z

Another solution is to use a Count Distinct:

Select ...
    , ( Select Count( Distinct column_1, column_2, column_3, column_4 )
        From TempTable ) As CountOfItems
From TempTable
Group By column_1, column_2, column_3, column_4

Solution 3:

I know it's rather late, but nobody's suggested this:

select count ( distinct column_1, column_2, column_3, column_4) 
from   temptable

This works in Oracle at least - I don't currently have other databases to test it out on, and I'm not so familiar with T-Sql and MySQL syntax.

Also, I'm not entirely sure whether it's more efficient in the parser to do it this way, or whether everyone else's solution of nesting the select statement is better. But I find this one to be more elegant from a coding perspective.

Solution 4:

I was trying to achieve the same without subquery and was able to get the required result as below

SELECT DISTINCT COUNT(*) OVER () AS TotalRecords
FROM temptable
GROUP BY column_1, column_2, column_3, column_4