SUM of grouped COUNT in SQL Query
SqlSql Problem Overview
I have a table with 2 fields:
ID NameI want to group them by name, with 'count', and a row 'SUM'
1 Alpha 2 Beta 3 Beta 4 Beta 5 Charlie 6 Charlie
Name CountHow would I write a query to add SUM row below the table?
Alpha 1 Beta 3 Charlie 2 SUM 6
Sql Solutions
Solution 1 - Sql
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
Solution 2 - Sql
SELECT name, COUNT(name) AS count, SUM(COUNT(name)) OVER() AS total_count
FROM Table GROUP BY name
Solution 3 - Sql
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
Solution 4 - Sql
Try this:
SELECT ISNULL(Name,'SUM'), count(*) as Count
FROM table_name
Group By Name
WITH ROLLUP
Solution 5 - Sql
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 ;
Solution 6 - Sql
Please run as below :
Select sum(count)
from (select Name,
count(Name) as Count
from YourTable
group by Name); -- 6
Solution 7 - Sql
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
Solution 8 - Sql
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
Solution 9 - Sql
For Sql server you can try this one.
SELECT ISNULL([NAME],'SUM'),Count([NAME]) AS COUNT
FROM TABLENAME
GROUP BY [NAME] WITH CUBE
Solution 10 - Sql
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
Solution 11 - Sql
You can use ROLLUP
select nvl(name, 'SUM'), count(*)
from table
group by rollup(name)
Solution 12 - Sql
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
Solution 13 - Sql
Use it as
select Name, count(Name) as Count from YourTable
group by Name
union
Select 'SUM' , COUNT(Name) from YourTable
Solution 14 - Sql
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
Solution 15 - Sql
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`;
Solution 16 - Sql
After the query, run below to get the total row count
select @@ROWCOUNT
Solution 17 - Sql
select sum(s) from (select count(Col_name) as s from Tab_name group by Col_name having count(*)>1)c