SQL Summary Of Columns by Group

Dev Shed Forums Sponsor:

December 2nd, 2011, 06:14 AM

Dapa

Registered User

Join Date: Dec 2011

Posts: 6

Time spent in forums: 2 h 12 m 47 sec
Reputation 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

December 2nd, 2011, 06:19 AM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,685

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 56 m 12 sec
Reputation Power: 284

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

December 2nd, 2011, 06:38 AM

Dapa

Registered User

Join Date: Dec 2011

Posts: 6

Time spent in forums: 2 h 12 m 47 sec
Reputation Power: 0

Quote:

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

December 2nd, 2011, 07:41 AM

Dapa

Registered User

Join Date: Dec 2011

Posts: 6

Time spent in forums: 2 h 12 m 47 sec
Reputation 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

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

December 5th, 2011, 01:40 PM

Dapa

Registered User

Join Date: Dec 2011

Posts: 6

Time spent in forums: 2 h 12 m 47 sec
Reputation Power: 0

solved the problem using DISTINCT. thanks for your help anyway.

SQL Summary Of Columns by Group

Developer Shed Advertisers and Affiliates