Can I Show Multiple "WHERE" Filtered Results In A Single Query/Output Table?

Question

I am using Postgres 9.2.4 on Red Hat 6.

Tables

---------------------------------------
|             Table 1                 |
---------------------------------------
| WorkflowID | DataSet | CreationTime |
---------------------------------------

------------------------------------
|             Table 2              |
------------------------------------
| WorkflowID | Data | CreationTime |
------------------------------------

Description

I'd like to be able to create a query which, for a list of records in Table 1, aggregates the WorkflowID COUNT/SUM(LENGTH(data)) from Table2. That's easy enough with:

SELECT b.WorkflowID,
       COUNT(b.*),
       SUM(LENGTH(b.data)/1024.)::numeric(10,2) AS size_kb
FROM Table1 a
JOIN Table2 b
  ON (a.WorkflowID = b.WorkflowID)
GROUP BY b.WorkflowID
ORDER BY size_kb DESC;

Now, I'd like to compare these WorkflowID contributions before-and-after a given CreationTime and have it in a single query and output table. Something like:

----------------------------------------------------------------------------
| WorkflowID | count_before | size_before_kb | count_after | size_after_kb |
----------------------------------------------------------------------------

My Problem

To my less-than-ideal understanding of SQL's mysteries, this looks like some kind of multi-WHERE clause type of query. Does that even exist, or would I have to run multiple queries and then merge the output?

Answer 1

I think you are looking for conditional aggregation:

SELECT b.WorkflowID,
       count(case when a.creationtime <= timestamp '2015-06-10 14:00:00' then b.worflowid end) as count_before,
       sum(  case when a.creationtime <= timestamp '2015-06-10 14:00:00' then (LENGTH(b.data)/1024.)::numeric(10,2) end) as size_before
       count(case when a.creationtime > timestamp '2015-06-10 14:00:00' then b.worflowid end) as count_after,
       sum(  case when a.creationtime > timestamp '2015-06-10 14:00:00' then (LENGTH(b.data)/1024.)::numeric(10,2) end) as size_after
FROM Table1 a
JOIN Table2 b
  ON (a.WorkflowID = b.WorkflowID)
GROUP BY b.WorkflowID;

Answer 2

You can OR where clauses to get different sets of filtered data in one result set, but you can't combine multiple aggregations in a single query, unless you use sub-selects. Another option is to collect results from different aggregations into temporary tables and then join the details into a single output.