Count number of records returned by group by

Question

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.

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

Solution 5

How about:

SELECT count(column_1)
FROM
    (SELECT * FROM temptable
    GROUP BY column_1, column_2, column_3, column_4) AS Records

View more solutions

Comments

[Chris]

@LorenVS: But that would give me a count of the number of records in the table. I need number of records after the group by happens.

[LorenVS]

The group by doesn't change the number of rows though. 1 + 1 + 2 (in your example) will be the number of rows in the table. Are you looking for 3? The number of distinct groups?

[Julian]

Another way to formulate the question: how do I select the number of distinct grouping levels for a given query?

[Ken Forslund]

It's not always obvious why a user asks a question, but I got here because I'm testing if a column in a view is a candidate primary key or combination key. "select count(distinct COLUMNNAME) from VIEWNAME" times out, where group by works if I can get a total.

[Bjinse]

I know this is a SQL-Server question, but for reference: This does not work on DB/2 (in my case on IBM iSeries). See my comment at Thomas´s answer

[Bjinse]

The first answer also works on DB/2, but for some reason it needs the addition AS TMP to work (like troutinator added)

[Thomas]

@Bjinse - Some DBMS will require that all derived tables have an alias. They all will accept it so it won't hurt to include it. I'll add it to my answer.

[McDan Garrett]

How would I echo that counted number?

[ZygD]

@McDanGarrett: what do you mean sorry?

[cartbeforehorse]

This down side of this solution is that it gives you the answer multiple times (for each combination of column_1, column_2, column_3, column_4). This may or may not be a significant side-effect, depending on how you process the results.

[Răzvan Flavius Panda]

Thomas added your solution to his answer. Anyway. I would not recommend ever doing this for maintainance reasons, the other solutions are much nicer.

[A. Greensmith]

Cannot run aggregate inside an aggregate on sql-server.

[Julian]

What value will BandedItemCount contain exactly? Does it differ between output rows? The asker is looking for the number of distinct grouping levels.

[Joe Aldrich]

In my case using TOP(1) COUNT() OVER() had poor query performance. Since I only needed the count of the groups I changed this to DISTINCT COUNT() OVER(), and the query performance improved dramatically.

[cartbeforehorse]

@RăzvanFlaviusPanda 1. Why? What's nicer about the other solutions? Nesting SQL is more verbose and in my eyes, more messy and harder to understand (ergo harder to maintain in a support sense). I get that you may have a preference for the other ways, but that's not a reason to "recommend" it over someone else's preference. Thomas did make a similar suggestion, yes, but again he makes it look as though nesting the SQL is a necessary part of the solution, which it isn't.

[Sheldore]

@GuilhermeCamposHazan : Can you be more precise? Where to add this line select top(1) count(*) over () as ....? There are already 2 count(*) in the answer. Where to add the third one?

[Ivan Reshetnikov]

CTE = Common Table Expressions

[Cempoalxóchitl]

Here's what I ended up doing: DISTINCT COUNT(ColumnA) OVER(PARTITION BY ColumnB)