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

    Join Date
    Dec 2011
    Posts
    6
    Rep Power
    0

    SQL Summary Of Columns by Group


    Hello,

    I'm pretty new to the world of SQL and I'm trying to create a query to give a summary of certain columns. I've tried numerous ways but I just cant figure it out.

    My table is like below:

    Code:
    Status | REF  | Charging Code | QTY |
    ------------------------------------
    R        | 0001 | CHG-001        | 2     |
    ------------------------------------
    P        | 0002 | CHG-001        | 1     |
    ------------------------------------
    R        | 0003 | CHG-002        | 5     |
    ------------------------------------
    R        | 0004 | CHG-001        | 2     |
    ------------------------------------
    P        | 0005 | CHG-003        | 1     |
    ------------------------------------
    S        | 0006 | CHG-001        | 22   |
    ------------------------------------
    What would be the query to give a count result like below:

    Code:
    Charging Code | Status_R | Status_P | Status_S
    -----------------------------------------------
    CHG-001        | 2            | 1           | 1
    -----------------------------------------------
    CHG-002        | 1            | 0           | 0
    -----------------------------------------------
    CHG-003        | 0            | 1           | 0
    Any help would be greatly appreciated, hope the above makes sense.

    Thanks
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,781
    Rep Power
    348
    Code:
    SELECT charging_code,
           sum(case 
                 when status = 'R' then 1 
                 else 0
               end)  as Status_R,
           sum(case 
                 when status = 'P' then 1 
                 else 0
               end)  as Status_P,
           sum(case 
                 when status = 'S' then 1 
                 else 0
               end)  as Status_S
    FROM your_table
    GROUP BY charging_code
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    6
    Rep Power
    0

    Thumbs up


    Originally Posted by shammat
    Code:
    SELECT charging_code,
           sum(case 
                 when status = 'R' then 1 
                 else 0
               end)  as Status_R,
           sum(case 
                 when status = 'P' then 1 
                 else 0
               end)  as Status_P,
           sum(case 
                 when status = 'S' then 1 
                 else 0
               end)  as Status_S
    FROM your_table
    GROUP BY charging_code
    Brilliant, thanks for your help
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    6
    Rep Power
    0
    Using the same logic as above, how would I return a count of unique values in column REF based on the status of S, for example table below will a few fields with the same REF.
    Code:
    Status | REF  | Charging Code | QTY |
    ------------------------------------
    R        | 0001 | CHG-001        | 2     |
    ------------------------------------
    P        | 0002 | CHG-001        | 1     |
    ------------------------------------
    R        | 0003 | CHG-002        | 5     |
    ------------------------------------
    R        | 0004 | CHG-001        | 2     |
    ------------------------------------
    P        | 0005 | CHG-003        | 1     |
    ------------------------------------
    S        | 0006 | CHG-001        | 22   |
    ----------------------------------- 
    S        | 0006 | CHG-001        | 5
    to

    Code:
    Charging Code | Status_R | Status_P | UNIQUE_S | SUM_QTY_S
    -----------------------------------------------
    CHG-001        | 2            | 1           | 1           | 27
    -----------------------------------------------
    CHG-002        | 1            | 0           | 0           | 0
    -----------------------------------------------
    CHG-003        | 0            | 1           | 0           | 0
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2011
    Posts
    6
    Rep Power
    0
    solved the problem using DISTINCT. thanks for your help anyway.

IMN logo majestic logo threadwatch logo seochat tools logo