基于来自同一表的不同标准，在同一字段上显示计数

Say my table has only 2 fields: Id and color.

假设我的表只有两个字段:Id和颜色。

id  color
----------
1   Green
2   Red
3   Yellow

I want the count to be displayed in a row not in a column.

我希望计数显示在一行中，而不是列中。

  SELECT COUNT(color), color
    FROM MYTABLE
GROUP BY color

This gives me:

这给我:

count   color
--------------
10      Green
20      Red
15      Yellow

I need it to be displayed as:

我需要它显示为:

Green  Red  Yellow
-------------------
10     20   15

4 个解决方案

#1

SELECT
  COUNT(CASE Color WHEN 'Green'  THEN 1 END) AS Green,
  COUNT(CASE Color WHEN 'Red'    THEN 1 END) AS Red,
  COUNT(CASE Color WHEN 'Yellow' THEN 1 END) AS Yellow
FROM MYTABLE

You don't need grouping here. Basically, every COUNT passes all the rows but counts only those where Color matches a certain value.

这里不需要分组。基本上，每个计数都传递所有行，但只计数颜色匹配某个值的行。

#2

select (select COUNT(color) from MyTable where color='Green') as 'Green', 
       (select COUNT(color) from MyTable where color='Red') as 'Red'. 
       (select COUNT(color) from MyTable where color='Yellow') as 'Yellow'

#3

Another way to do this (in MySQL only) is:

另一种方法(仅在MySQL中)是:

SELECT SUM(color = 'Green')  AS "Green"
     , SUM(color = 'Red')    AS "Red"
     , SUM(color = 'Yellow') AS "Yellow"
FROM MyTable ;

#4

Try this link http://blog.programmingsolution.net/sql-server-2008/sql-server-pivot-converting-rows-to-columns-with-dynamic-query/

试试这个链接http://blog.programmingsolution.net/sql-server-2008/sql-server-pivot-converting-rows-to-columns-with-dynamic-query/

but if you do somthing like this

但是如果你做这样的事

 select [ID],[COLOUR] from

 ( SELECT [ID],[COLOUR] FROM [dbo].[table] ) as S

 Pivot ( COUNT([COLOUR])

 FOR COLOUR IN ([YELLOW],[RED],[GREEN])

 ) as P

#1

SELECT
  COUNT(CASE Color WHEN 'Green'  THEN 1 END) AS Green,
  COUNT(CASE Color WHEN 'Red'    THEN 1 END) AS Red,
  COUNT(CASE Color WHEN 'Yellow' THEN 1 END) AS Yellow
FROM MYTABLE