0

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?

1
  • Kindly provide DDL (CREATE TABLE blah (field_1, type_1.....);) and DML (INSERT INTO blah VALUES(.......)), so that those who wish to help you can.
    – Vérace
    Jun 10 '15 at 13:50
3

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;
1
  • Another option would be to use a CTE .. not sure which would perform better.. Jun 10 '15 at 15:38
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.