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.

Abelmosk

@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. – Abelmosk

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

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

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. – Citystate

For future readers, since the question is not very clear: OP wants a count of distinct groups. – Auction

Answer 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

Belsky

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

How would I echo that counted number? – Venn

@McDanGarrett: what do you mean sorry? – Belsky

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. – Sighted

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. – Petulah

@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? – Uninterrupted

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

Answer 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

Icterus

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

@Brunell - 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. – Icterus

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

Sighted

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

@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. – Sighted

Answer 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

Brewster

Answer 5

How about:

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

Nucleonics

Answer 6

A CTE worked for me:

with cte as (
  select 1 col1
  from temptable
  group by column_1
)

select COUNT(col1)
from cte;

Hyoscyamine

CTE = Common Table Expressions – Nickens

Answer 7

In PostgreSQL this works for me:

select count(count.counts) 
from 
    (select count(*) as counts 
     from table 
     group by concept) as count;

Disserve

Answer 8

Try this query:

select top 1 TotalRows = count(*) over () 
from yourTable
group by column1, column2

Least

Answer 9

You could do:

select sum(counts) total_records from (
    select count(*) as counts
    from temptable
    group by column_1, column_2, column_3, column_4
) as tmp

Enhanced

Answer 10

Can you execute the following code below. It worked in Oracle.

SELECT COUNT(COUNT(*))
FROM temptable
GROUP BY column_1, column_2, column_3, column_4

Analog

Cannot run aggregate inside an aggregate on sql-server. – Subsidize

Answer 11

you can also get by the below query

select column_group_by,count(*) as Coulm_name_to_be_displayed from Table group by Column;

-- For example:
select city,count(*) AS Count from people group by city

Outskirts

Answer 12

Here what i did

SELECT COUNT(OwnerID) FROM (SELECT OwnerID FROM #resultsTable GROUP BY OwnerID) AS c

Featherveined

Answer 13

In a temp table of Purchase_Orders and Releases

select T1.po , count(T1.release_kk)

from #T1 T1

group by T1.po

Fresh

Answer 14

How about using a COUNT OVER (PARTITION BY {column to group by}) partitioning function in SQL Server?

For example, if you want to group product sales by ItemID and you want a count of each distinct ItemID, simply use:

SELECT
{columns you want} ,
COUNT(ItemID) OVER (PARTITION BY ItemID) as BandedItemCount ,
{more columns you want}... ,
FROM {MyTable}

If you use this approach, you can leave the GROUP BY out of the picture -- assuming you want to return the entire list (as you might do report banding where you need to know the entire count of items you are going to band without having to display the entire set of data, i.e. Reporting Services).

Usher

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

Answer 15

Following for PrestoDb, where FirstField can have multiple values:

select *
            , concat(cast(cast((ThirdTable.Total_Records_in_Group * 100 / ThirdTable.Total_Records_in_baseTable) as DECIMAL(5,2)) as varchar), '%') PERCENTage
from 
(
    SELECT FirstTable.FirstField, FirstTable.SecondField, SecondTable.Total_Records_in_baseTable, count(*) Total_Records_in_Group
    FROM BaseTable FirstTable
    JOIN (
            SELECT FK1, count(*) AS Total_Records_in_baseTable 
            FROM BaseTable
            GROUP BY FK1
        ) SecondTable
    ON FirstTable.FirstField = SecondTable.FK1
    GROUP BY FirstTable.FirstField, FirstTable.SecondField, SecondTable.Total_Records_in_baseTable
    ORDER BY FirstTable.FirstField, FirstTable.SecondField
) ThirdTable

Corrianne