SQL查询中分组计数的总和

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'

我想把它们命名为" count "和" SUM "

Name     Count
-------  -----
Alpha     1
Beta      3
Charlie   2
SUM       6

How would I write a query to add SUM row below the table?

如何编写查询来在表下面添加和行?

13 个解决方案

#1

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

#2

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

#3

Without specifying which rdbms you are using

没有指定要使用的rdbms

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

这是一个使用ROLLUP的汇总数据的SQL SERVER版本。

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

#4

Try this:

试试这个:

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

#5

Please run as below :

请如下:

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

#6

You can use union to joining rows.

您可以使用union来连接行。

select Name, count(*) as Count from yourTable group by Name
union all
select "SUM" as Name, count(*) as Count from yourTable

#7

For Sql server you can try this one.

对于Sql server，您可以尝试这个。

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

#8

  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

#9

You can use ROLLUP

您可以使用汇总

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

#10

You can try group by on name and count the ids in that group.

您可以按名称尝试组，并计算组中的id。

SELECT name, count(id) as COUNT FROM table group by name

#11

Use it as

把它作为

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

#12

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

在查询之后，运行下面以获取行总数

select @@ROWCOUNT

#13

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

选择sum(s)(选择count(Col_name)作为s，从Tab_name组中通过Col_name具有count(*)>1)c