Count number of records returned by group by

Chris picture Chris · Feb 28, 2011 · Viewed 280.4k times · Source

How do I count the number of records returned by a group by query,

For eg:

select count(*) 
from temptable
group by column_1, column_2, column_3, column_4

Gives me,

1
1
2

I need to count the above records to get 1+1+1 = 3.

Answer

gbn picture gbn · Feb 28, 2011

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