#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    1
    Rep Power
    0

    Help with counting various colours


    Hi,

    I'm new to SQL - please bear with me.

    I'd like to return how many instances there are of Red and Blue when the ProjName is Example 1, and then in a separate column, show how many instances there are of Green when the ProjName is Example 1.

    See below for the example data

    ProjName Result DateTime
    Example 1 Red 02/10/2012 08:00
    Example 2 Red 02/10/2012 08:00
    Example 1 Blue 02/10/2012 08:00
    Example 3 Blue 02/10/2012 08:00
    Example 4 Green 02/10/2012 08:00
    Example 2 Green 02/10/2012 08:00
    Example 4 Red 02/10/2012 08:00
    Example 1 Red 03/10/2012 08:00
    Example 2 Blue 03/10/2012 08:00
    Example 1 Green 03/10/2012 08:00
    Example 3 Green 03/10/2012 08:00
    Example 4 Green 03/10/2012 08:00
    Example 2 Red 03/10/2012 08:00
    Example 1 Red 03/10/2012 08:00
    Example 1 Blue 04/10/2012 08:00
    Example 2 Blue 04/10/2012 08:00

    I'd like to return the below

    ProjName | Colour | Colour 2
    Example 1 | 5 | 1

    I'm currently using the following query which is works fine for returning anything thats Red or Blue, but I don't know how to get green and add it it into another column (it might not be exactly correct as I had to write it from memory)

    SELECT ProjName, COUNT (*) 'Result' as Colour

    FROM ExampleDB

    WHERE (Result='Red' or 'Blue')
    and (DateTime between '2012-oct-02' and '2012-oct-03)

    GROUP BY ProjName



    Any help is greatly appreciated.

    Thank you
  2. #2
  3. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    Code:
    SELECT 
      ProjName, 
    COUNT (CASE WHEN Result IN ('Red','Blue') 
      THEN 1 ELSE NULL END)  AS RedBlue,
    
    COUNT (CASE WHEN Result='Green'
      THEN 1 ELSE NULL END)  AS Green
    
    FROM 
      ExampleDB
    WHERE
      DateTime BETWEEN '2012-10-02' and '2012-10-03'
    
    GROUP BY ProjName
    A few things to note
    a) SQL is the language but not the specific database. if you are using MySQL then your question belongs in this forum. If on the other hand you are using Oracle or Postgres or MSSQL then you would want your query in another forum

    b) Don't put quotes around a column name like this 'Results' that makes it a string and not a column name, thus the word Results would never match a colour since Results is not the word Green

    c) If you are using mysql then you must be using a CHAR or VARCHAR field for your dates, you should use DATE type and format your dates as yyyy-mm-dd or some other acceptable format. i've assumed you would do that and changed your date values accordingly. if you are using MSSQL, you could use the dates as is as I believe they are acceptable in that format for that database.
    Last edited by Guelphdad; October 2nd, 2012 at 07:15 PM.

IMN logo majestic logo threadwatch logo seochat tools logo