Geek asks and answers

Number of views in one field based on different criteria from one table

Say my table has only 2 fields: Idand color.

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

I want the graph to appear 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 display as:

Green  Red  Yellow
-------------------
10     20   15
+3
source share
4 answers
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 do not need grouping here. In principle, each COUNTpasses all the lines, but takes into account only those where it Colorcorresponds to a certain value.

+3
source 
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'
+1
source

( MySQL):

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

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

-

 select [ID],[COLOUR] from

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

 Pivot ( COUNT([COLOUR])

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

 ) as P
0

More articles:


All Articles