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?

17 Answers

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 

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

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 

Try this:

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

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 ; 

Please run as below :

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

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 

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 

For Sql server you can try this one.

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

 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 

You can use ROLLUP

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

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 

Use it as

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

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

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

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

select @@ROWCOUNT 

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