CODEWITHSUNDEEP
sql-servert-sqlcountgroup-by
Chris
Chris

Reputation: 3043

Count number of records returned by group by

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.

Upvotes: 211

Views: 416311

Answers (15)

Bill Brutzman
Bill Brutzman

Reputation: 21

In a temp table of Purchase_Orders and Releases

select T1.po , count(T1.release_kk)

from #T1 T1

group by T1.po

Upvotes: 0

pubudu sachintha
pubudu sachintha

Reputation: 753

Here what i did

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

Upvotes: 0

SUKUMAR S
SUKUMAR S

Reputation: 335

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

Upvotes: -1

PedroC88
PedroC88

Reputation: 3829

How about:

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

Upvotes: 8

Johnny B
Johnny B

Reputation: 1

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

Upvotes: -1

Marimuthu Shanmugasundaram
Marimuthu Shanmugasundaram

Reputation: 21

Try this query:

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

Upvotes: 2

Nitish Sahoo
Nitish Sahoo

Reputation: 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

Upvotes: 0

Arif
Arif

Reputation: 89

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

Upvotes: 1

Manish Mulani
Manish Mulani

Reputation: 7375

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

Upvotes: 25

omar
omar

Reputation: 1601

In PostgreSQL this works for me:

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

Upvotes: 3

SQL Pete
SQL Pete

Reputation: 51

A CTE worked for me:

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

select COUNT(col1)
from cte;

Upvotes: 5

Thomas
Thomas

Reputation: 64635

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

Upvotes: 106

cartbeforehorse
cartbeforehorse

Reputation: 3467

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.

Upvotes: 32

gbn
gbn

Reputation: 432210

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

Upvotes: 231

troutinator
troutinator

Reputation: 1198

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

Upvotes: 1

Related Questions