Reputation: 1501

SUM of grouped COUNT in SQL Query

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?

Upvotes: 107

Answers (17)

Juls

Reputation: 688

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

Upvotes: 5

Itay wazana

Reputation: 259

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 ;

Upvotes: 9

theBuzzyCoder

Reputation: 2880

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`;

Upvotes: 0

JohnCanessa

Reputation: 39

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

Upvotes: 0

Vishal Suthar

Reputation: 17193

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

Upvotes: 109

Koy Bun

Reputation: 201

Try this:

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

Upvotes: 14

Yitzchok Glancz

Reputation: 1400

You can use ROLLUP

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

Upvotes: 1

Masum

Reputation: 119

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

Upvotes: -6

Masum

Reputation: 119

  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

Upvotes: 1

Venkatesh Muthuswamy

Reputation: 43

Please run as below :

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

Upvotes: 5

Chitta

Reputation: 206

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

select @@ROWCOUNT

Upvotes: -2

Yitzchok Glancz

Reputation: 1400

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

Upvotes: 122

sapan

Reputation: 21

For Sql server you can try this one.

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

Upvotes: 2

Usman

Reputation: 3278

Use it as

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

Upvotes: 0

Adriaan Stander

Reputation: 166376

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

Upvotes: 20

Habibillah

Reputation: 28695

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

Upvotes: 2

Htaras

Reputation: 879

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

Upvotes: -2

SUM of grouped COUNT in SQL Query

Answers (17)

SQL Fiddle DEMO

SQL Fiddle DEMO

Related Questions