SQL如何使用WXY和Z的Count和Group

Let's say I'm trying to count the number of elements in W, X, Y, and Z. I don't care about specific numbers within WXY, and I only care about Z. is there a way I can make something like this:

假设我正在尝试计算W,X,Y和Z中的元素数量。我不关心WXY中的具体数字,我只关心Z.有没有办法我可以做这样的事情:

column | count
---------------
 WXY   |  10
  Z    |  5

instead of

column | count
---------------
  W    |  2
  X    |  3
  Y    |  5
  Z    |  5

3 个解决方案

#1

SELECT [column] = CASE 
  WHEN [column] IN ('W','X','Y') THEN 'WXY' ELSE 'Z' END,
  [count] = COUNT(*)
FROM dbo.[table]
WHERE [column] IN ('W','X','Y','Z')
  GROUP BY CASE 
  WHEN [column] IN ('W','X','Y') THEN 'WXY' ELSE 'Z' END
ORDER BY [column];

#2

SQL Fiddle

select case 
    when c in ('W', 'X', 'Y') then 'XYZ'
    else 'Z' end as c,
    count(*)
from t
group by 1

#3

You can do this easily with a Common Table Expression (CTE) and aggregate the data before trying to get a count of the records.

您可以使用公用表表达式(CTE)轻松完成此操作,并在尝试获取记录计数之前聚合数据。

It works with both PostgreSQL and SQL Server (but there are problem if you name a column "column").

它适用于PostgreSQL和SQL Server(但如果您将列命名为“列”,则会出现问题)。

PostgreSQL: SQL Fiddle SQL Server: SQL Fiddle

PostgreSQL:SQL小提琴SQL Server:SQL小提琴

;with aggData as
(
   select case 
             when c = 'Z' then 'Z'
             else 'WXY' 
          end as col
   from t
)
select col, count(*)
from aggData
group by col

Here is documentation on using CTEs with SQL Server and CTEs with PostGreSQL.

以下是使用带有SQL Server和CTE和PostGreSQL的CTE的文档。

#1

SELECT [column] = CASE 
  WHEN [column] IN ('W','X','Y') THEN 'WXY' ELSE 'Z' END,
  [count] = COUNT(*)
FROM dbo.[table]
WHERE [column] IN ('W','X','Y','Z')
  GROUP BY CASE 
  WHEN [column] IN ('W','X','Y') THEN 'WXY' ELSE 'Z' END
ORDER BY [column];

#2