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?

Answer 1

SELECT name, COUNT(name) AS count, SUM(COUNT(name)) OVER() AS total_count
FROM Table GROUP BY name

Answer 2

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

Answer 3

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

Answer 4

Try this:

SELECT  ISNULL(Name,'SUM'), count(*) as Count
FROM table_name
Group By Name
WITH ROLLUP

Answer 5

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) > 1) as t1 ;

Answer 6

Please run as below :

Select sum(count) 
  from (select Name, 
               count(Name) as Count 
          from YourTable
      group by Name);  -- 6

Answer 7

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

Answer 8

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

Answer 9

For Sql server you can try this one.

SELECT ISNULL([NAME],'SUM'),Count([NAME]) AS COUNT
FROM TABLENAME
GROUP BY [NAME] WITH CUBE

Answer 10

  with cttmp
  as
  (
  select Col_Name, count(*) as ctn from tab_name group by Col_Name having count(Col_Name)>1
  )
  select sum(ctn) from c

Answer 11

You can use ROLLUP

select nvl(name, 'SUM'), count(*)
from table
group by rollup(name)

Answer 12

Use it as

select Name, count(Name) as Count from YourTable
group by Name
union 
Select 'SUM' , COUNT(Name) from YourTable

Answer 13

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

Answer 14

I required having count(*) > 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`) > 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`) > 1) as `tmp`;

Answer 15

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

Answer 16

After the query, run below to get the total row count

select @@ROWCOUNT

Answer 17

select sum(s) from (select count(Col_name) as s from Tab_name group by Col_name having count(*)>1)c