Subtotal SUMs in different columns

[Aidan Furlan]

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:

       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:

 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

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?

[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:

SELECT   employee_id, 
         sum(CASE WHEN CATEGORY='standard' THEN end_time - start_time ELSE 0 END) AS standard,
         sum(CASE WHEN CATEGORY='weekend' THEN end_time - start_time ELSE 0 END) AS weekend,
         sum(CASE WHEN CATEGORY='holiday' THEN end_time - start_time ELSE 0 END) AS holiday
FROM     SHIFT
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:

qrySHIFT
SELECT   employee_id, start_time, end_time, 
         CASE WHEN CATEGORY='standard' THEN 1 else 0 END as Is_Standard,
         CASE WHEN CATEGORY='weekend'  THEN 1 else 0 END as Is_weekend,
         CASE WHEN CATEGORY='holiday'  THEN 1 else 0 END as Is_holiday
FROM     SHIFT;


SELECT   employee_id, 
         sum((end_time - start_time) * is_standard) AS standard,
         sum((end_time - start_time) * is_weekend)  AS weekend,
         sum((end_time - start_time) * is_holiday)  AS holiday,
FROM     qrySHIFT
GROUP BY employee_id;

Hope this gives some ideas for trying out potential solutions that may work for you.

-Stewart

[Aidan Furlan]

Hi Stewart, I implemented the query following the first example you gave and it worked brilliantly. Thanks so much!