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

User · Answer

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

User · Answer

In PostgreSQL this works for me   select count count counts   from       select count    as counts       from table       group by concept  as count

User · Answer

A CTE worked for me   with cte as     select 1 col1   from temptable   group by column 1    select COUNT col1  from cte

User · Answer

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

User · Answer

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

User · Answer

How about   SELECT count column 1  FROM      SELECT   FROM temptable     GROUP BY column 1  column 2  column 3  column 4  AS Records

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Try this query   select top 1 TotalRows   count    over     from yourTable group by column1  column2

User · Answer

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

User · Answer

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

User · Answer

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

[sql-server] Count number of records returned by group by

Following for PrestoDb, where FirstField can have multiple values:

Examples related to sql-server

Examples related to tsql

Examples related to count

Examples related to group-by