By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,515 Members | 1,363 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,515 IT Pros & Developers. It's quick & easy.

Subtotal SUMs in different columns

P: 2
Is there an easy way to do multiple SUMs in the one query, with different criteria used for each SUM, effectively resulting in a set of subtotal columns?

Here is a simple example of what I am talking about. Say we have the following table structure to store employee shifts:

Expand|Select|Wrap|Line Numbers
  1.        Column       |     Type              |   Modifiers                                
  2. --------------------+-----------------------+-------------------------------
  3.  shift_id           | integer               | not null
  4.  employee_id        | integer               | not null
  5.  shift_date         | date                  | not null
  6.  start_time         | numeric(4,2)          | not null
  7.  end_time           | numeric(4,2)          | not null
  8.  shift_category     | character varying(32) |
  9.  
What I want is to sum shift lengths grouped by shift_category, but have the subtotals appear as separate columns, instead of getting extra rows as you would with a GROUP BY. Eg my output should look like this:

Expand|Select|Wrap|Line Numbers
  1.  employee_id | standard | weekend | holiday   
  2. -------------+----------+---------+--------
  3.          763 |   150.00 |   14.50 |   9.50
  4.          107 |   145.25 |   12.25 |   6.25
  5.          784 |   167.00 |   31.00 |   6.00
  6.  
I know I could do this by joining on the table 3 times, eg

Expand|Select|Wrap|Line Numbers
  1. SELECT * FROM
  2.     (select employee_id, SUM(end_time - start_time) 
  3.     FROM shift WHERE category='standard' 
  4.     GROUP BY employee_id) AS standard, 
  5.     (select employee_id, SUM(end_time - start_time) 
  6.     FROM shift WHERE category='weekend' 
  7.     GROUP BY employee_id) AS weekend, 
  8.     (select employee_id, SUM(end_time - start_time) 
  9.     FROM shift WHERE category='holiday' 
  10.     GROUP BY employee_id) AS holiday 
  11. WHERE standard.employee_id=weekend.employee_id AND weekend.employee_id=holiday.employee_id;
  12.  
However, in practice I am not doing this on a single table but a subquery which is already pretty massive, so I wanted to avoid doing it like this. Is there some elegant way of achieving what I am after?
May 25 '11 #1

✓ answered by Stewart Ross

I'm not a POSTGRESQL programmer, but I note that POSTGRESQL has a CASE WHEN construct which, if it can be used as an expression within a SUM, may be able to do what you need it to do:

Expand|Select|Wrap|Line Numbers
  1. SELECT   employee_id, 
  2.          sum(CASE WHEN CATEGORY='standard' THEN end_time - start_time ELSE 0 END) AS standard,
  3.          sum(CASE WHEN CATEGORY='weekend' THEN end_time - start_time ELSE 0 END) AS weekend,
  4.          sum(CASE WHEN CATEGORY='holiday' THEN end_time - start_time ELSE 0 END) AS holiday
  5. FROM     SHIFT
  6. GROUP BY employee_id;
The CASE statements are just being used to act as selectors for what is being summed, and are the equivalents of IIF statements in MS-Access (that I use in this way often to provide such selective summing) and IF statements in other SQL dialects.

I believe the use I've suggested is legal in POSTGRESQL (the manual states that 'CASE clauses can be used wherever an expression is valid'), but there are alternatives involving the use of a separate query which would work if not.

For example, instead of basing the SUM query directly on table SHIFT, base it on another query which uses the CASE statement to return a 1 or 0 for each criterion:

Expand|Select|Wrap|Line Numbers
  1. qrySHIFT
  2. SELECT   employee_id, start_time, end_time, 
  3.          CASE WHEN CATEGORY='standard' THEN 1 else 0 END as Is_Standard,
  4.          CASE WHEN CATEGORY='weekend'  THEN 1 else 0 END as Is_weekend,
  5.          CASE WHEN CATEGORY='holiday'  THEN 1 else 0 END as Is_holiday
  6. FROM     SHIFT;
  7.  
  8.  
  9. SELECT   employee_id, 
  10.          sum((end_time - start_time) * is_standard) AS standard,
  11.          sum((end_time - start_time) * is_weekend)  AS weekend,
  12.          sum((end_time - start_time) * is_holiday)  AS holiday,
  13. FROM     qrySHIFT
  14. GROUP BY employee_id;
Hope this gives some ideas for trying out potential solutions that may work for you.

-Stewart

Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
I'm not a POSTGRESQL programmer, but I note that POSTGRESQL has a CASE WHEN construct which, if it can be used as an expression within a SUM, may be able to do what you need it to do:

Expand|Select|Wrap|Line Numbers
  1. SELECT   employee_id, 
  2.          sum(CASE WHEN CATEGORY='standard' THEN end_time - start_time ELSE 0 END) AS standard,
  3.          sum(CASE WHEN CATEGORY='weekend' THEN end_time - start_time ELSE 0 END) AS weekend,
  4.          sum(CASE WHEN CATEGORY='holiday' THEN end_time - start_time ELSE 0 END) AS holiday
  5. FROM     SHIFT
  6. GROUP BY employee_id;
The CASE statements are just being used to act as selectors for what is being summed, and are the equivalents of IIF statements in MS-Access (that I use in this way often to provide such selective summing) and IF statements in other SQL dialects.

I believe the use I've suggested is legal in POSTGRESQL (the manual states that 'CASE clauses can be used wherever an expression is valid'), but there are alternatives involving the use of a separate query which would work if not.

For example, instead of basing the SUM query directly on table SHIFT, base it on another query which uses the CASE statement to return a 1 or 0 for each criterion:

Expand|Select|Wrap|Line Numbers
  1. qrySHIFT
  2. SELECT   employee_id, start_time, end_time, 
  3.          CASE WHEN CATEGORY='standard' THEN 1 else 0 END as Is_Standard,
  4.          CASE WHEN CATEGORY='weekend'  THEN 1 else 0 END as Is_weekend,
  5.          CASE WHEN CATEGORY='holiday'  THEN 1 else 0 END as Is_holiday
  6. FROM     SHIFT;
  7.  
  8.  
  9. SELECT   employee_id, 
  10.          sum((end_time - start_time) * is_standard) AS standard,
  11.          sum((end_time - start_time) * is_weekend)  AS weekend,
  12.          sum((end_time - start_time) * is_holiday)  AS holiday,
  13. FROM     qrySHIFT
  14. GROUP BY employee_id;
Hope this gives some ideas for trying out potential solutions that may work for you.

-Stewart
May 27 '11 #2

P: 2
Hi Stewart, I implemented the query following the first example you gave and it worked brilliantly. Thanks so much!
May 30 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.