SUM of grouped COUNT in SQL Query

Question

I have a table with 2 fields    ID  Name --  ------- 1   Alpha 2   Beta 3   Beta 4   Beta 5   Charlie 6   Charlie   I want to group them by name  with  count   and a row  SUM    Name     Count -------  ----- Alpha     1 Beta      3 Charlie   2 SUM       6   How would I write a query to add SUM row below the table

User · Accepted Answer

SELECT name  COUNT name  AS count FROM table GROUP BY name  UNION ALL  SELECT  SUM  name  COUNT name  FROM table   OUTPUT   name                                               count -------------------------------------------------- ----------- alpha                                              1 beta                                               3 Charlie                                            2 SUM                                                6

User · Answer

Without specifying which rdbms you are using   Have a look at this demo  SQL Fiddle DEMO  SELECT Name  COUNT 1  as Cnt FROM Table1 GROUP BY Name UNION ALL SELECT  SUM  Name  COUNT 1  FROM Table1   That said  I would recomend that the total be added by your presentation layer  and not by the database   This is a bit more of a SQL SERVER Version using Summarizing Data Using ROLLUP  SQL Fiddle DEMO  SELECT CASE WHEN  GROUPING NAME    1  THEN  SUM              ELSE ISNULL NAME   UNKNOWN          END Name         COUNT 1  as Cnt FROM Table1 GROUP BY NAME WITH ROLLUP

User · Answer

Try this   SELECT  ISNULL Name  SUM    count    as Count FROM table name Group By Name WITH ROLLUP

User · Answer

You can use ROLLUP      select nvl name   SUM    count    from table group by rollup name

User · Answer

I required having count     gt  1 also  So  I wrote my own query after referring some the above queries  SYNTAX   select sum count  from  select count  table name   id   as  count  from  table name  where  some condition  group by  some column  having count  table name   id    gt  1  as  tmp     Example   select sum count  from  select count  table name   id   as  count  from  table name  where  table name   name  IS NOT NULL and  table name   name        group by  table name   name  having count  table name   id    gt  1  as  tmp

User · Answer

You can use union to joining rows   select Name  count    as Count from yourTable group by Name union all select  SUM  as Name  count    as Count from yourTable

User · Answer

I am using SQL server and the following should work for you   select cast name as varchar 16   as  Name   count name  as  Count    from Table1   group by Name   union all   select  Sum    count name    from Table1

User · Answer

with cttmp   as       select Col Name  count    as ctn from tab name group by Col Name having count Col Name  gt 1       select sum ctn  from c

User · Answer

The way I interpreted this question is needing the subtotal value of each group of answers   Subtotaling turns out to be very easy  using PARTITION    SUM COUNT 0   OVER  PARTITION BY  Grouping   AS  MY TOTAL    This is what my full SQL call looks like   SELECT MAX GroupName   name   MAX AUX2  type     COUNT 0   count   SUM COUNT 0   OVER PARTITION BY GroupId  AS  total      FROM  MyView    WHERE Active 1 AND Type  APP  AND Completed 1     AND  Date  BETWEEN  01 01 2014  AND GETDATE       AND Id    5b9xxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx  AND GroupId IS NOT NULL   GROUP BY AUX2  GroupId   The data returned from this looks like   name    type    count   total Training Group 2    Cancelation 1   52 Training Group 2    Completed   41  52 Training Group 2    No Show 6   52 Training Group 2    Rescheduled 4   52 Training Group 3    NULL        4   10535 Training Group 3    Cancelation 857 10535 Training Group 3    Completed   7923    10535 Training Group 3    No Show 292 10535 Training Group 3    Rescheduled 1459    10535 Training Group 4    Cancelation 2   27 Training Group 4    Completed   24  27 Training Group 4    Rescheduled 1   27

User · Answer

select sum s  from  select count Col name  as s from Tab name group by Col name having count    1 c

User · Answer

After the query  run below to get the total row count  select   ROWCOUNT

User · Answer

SELECT name  COUNT name  AS count  SUM COUNT name   OVER   AS total count FROM Table GROUP BY name

User · Answer

You can try group by on name and count the ids in that group    SELECT name  count id  as COUNT FROM table group by name

User · Answer

all of the solution here are great but not necessarily can be implemented for old mysql servers  at least at my case   so you can use sub-queries  i think it is less complicated     select sum t1 cnt  from           SELECT column  COUNT column  as cnt             FROM             table              GROUP BY              column             HAVING              COUNT column   gt  1  as t1

User · Answer

For Sql server you can try this one   SELECT ISNULL  NAME   SUM   Count  NAME   AS COUNT FROM TABLENAME GROUP BY  NAME  WITH CUBE

User · Answer

Use it as   select Name  count Name  as Count from YourTable group by Name union  Select  SUM    COUNT Name  from YourTable

User · Answer

Please run as below    Select sum count     from  select Name                  count Name  as Count            from YourTable       group by Name    -- 6

[sql] SUM of grouped COUNT in SQL Query

Examples related to sql

Examples related to count

Examples related to sum