How can I aggregate two classes of values in a SQL query?

Question

I have a table containing intervals:

CREATE TABLE tbl (
    user_id: INTEGER,
    start: TIMESTAMP,
    end: TIMESTAMP,
    billable: BOOLEAN
);

I want to create a view that looks like this:

user_id | billable_time | unbillable_time

I can get one of these two options using the following query:

SELECT user_id, sum(end - start) AS billable_time WHERE billable = TRUE GROUP BY user_id;

However, I need both columns, with indefinite time being equivalent:

SELECT user_id, sum(end - start) AS unbillable_time WHERE billable = FALSE GROUP BY user_id;

How can I get these two values in one request?

+2

Chris r 10 Sep 09 at 17:38

2 answers

SELECT user_id, billable, sum(end - start) AS billable_time 
GROUP BY user_id,billable;

0

dnagirl 10 Sep 09 at 17:43

dnagirl · Accepted Answer · 2009-09-10T17:55:53+0000

select user_id, sum(case billable when 1 then (end - start) end) as can_bill,
  sum(case billable when 0 then (end - start) end) as unbillable

How can I aggregate two classes of values ​​in a SQL query?