display count on same field based on different criteria from same table

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

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:

cou开发者_StackOverflow中文版nt   color
--------------
10      Green
20      Red
15      Yellow

I need it to be displayed as:

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

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.

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'

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

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

Try this link 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