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'...
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
Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.
