429,515 Members | 1,363 Online
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        Column       |     Type              |   Modifiers                                 --------------------+-----------------------+-------------------------------  shift_id           | integer               | not null  employee_id        | integer               | not null  shift_date         | date                  | not null  start_time         | numeric(4,2)          | not null  end_time           | numeric(4,2)          | not null  shift_category     | character varying(32) |   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  employee_id | standard | weekend | holiday    -------------+----------+---------+--------          763 |   150.00 |   14.50 |   9.50          107 |   145.25 |   12.25 |   6.25          784 |   167.00 |   31.00 |   6.00   I know I could do this by joining on the table 3 times, eg Expand|Select|Wrap|Line Numbers SELECT * FROM     (select employee_id, SUM(end_time - start_time)      FROM shift WHERE category='standard'      GROUP BY employee_id) AS standard,      (select employee_id, SUM(end_time - start_time)      FROM shift WHERE category='weekend'      GROUP BY employee_id) AS weekend,      (select employee_id, SUM(end_time - start_time)      FROM shift WHERE category='holiday'      GROUP BY employee_id) AS holiday  WHERE standard.employee_id=weekend.employee_id AND weekend.employee_id=holiday.employee_id;   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