Combine 2 SQL queries from the same table to single output

Question

I have a table, 'commercial_events' that contains both sales ('event_type = 'SALE'') and purchases ('event_type = 'PURCHASE'). So far I'm using two separate queries, to create tabulated output broken-down by the hour. I would like to be able to run a single query, and combine all the data in a single output.

Right now I'm running this query:

SELECT date_trunc('hour',event_tstamp) AS 'Hour', count(*) AS 'Purchases', sum(amount) AS 'Purchases Total'
FROM commercial_events
WHERE event_type = 'PURCHASE' AND state = 'COMPLETED'
GROUP BY 1
ORDER BY 1;

The output it returns looks like this:

         Hour           |   Purchases  |   Purchases Total
------------------------+--------------+--------------------
 2019-12-12 00:00:00+01 |          476 |  -533.582000000000
...

When doing the process manually, I also run the below query and combine the 2 outputs manually:

SELECT date_trunc('hour',event_tstamp) AS 'Hour', count(*) AS 'Sales', sum(amount) AS 'Sales Total'
FROM commercial_events
WHERE event_type = 'SALE' AND state = 'COMPLETED'
GROUP BY 1
ORDER BY 1;

But I want the 2 outputs to be combined, like this:

          Hour          |   Sales   |   Sales Total   |   Purchases   |   Purchases Total
------------------------+-----------+-----------------+---------------+--------------------
 2019-12-12 00:00:00+01 |   1173    |       2330      |      476      |  -533.582000000000
 ...

forpas · Accepted Answer · 2019-12-22 11:54:34Z

2

If your version is 9.4+ then you can use the FILTER clause:

SELECT 
  date_trunc('hour',event_tstamp) AS 'Hour', 
  COUNT(*) FILTER (WHERE event_type = 'PURCHASE') AS 'Purchases',
  SUM(Amount) FILTER (WHERE event_type = 'PURCHASE') AS 'Purchases_Total',
  COUNT(*) FILTER (WHERE event_type = 'SALE') AS 'Sales',
  SUM(Amount) FILTER (WHERE event_type = 'SALE') AS 'Sales_Total'
FROM commercial_events
WHERE event_type IN ('PURCHASE', 'SALE') AND state = 'COMPLETED'
GROUP BY date_trunc('hour',event_tstamp);

answered Dec 22, 2019 at 11:54

forpas

165k10 gold badges51 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

LukStorms Over a year ago

Didn't know of the FILTER. Not sure yet for what use-case it could be used that can't be solved with the more traditional cond. aggr. But it sure is interesting. :) +1

jjanes Over a year ago

@LukStorms I find the FILTER easier to read, as well as being less verbose in this case. Upon reading the conditional sum, you have to think about it more, especially if you are not the one who wrote it. And some aggregates (other than SUM) might not have a neutral value which can be included without changing the result.

LukStorms · Accepted Answer · 2019-12-22 11:40:48Z

1

Use conditional aggregation

SELECT date_trunc(ce."hour",event_tstamp) AS "Hour"
, count(case when event_type = 'SALE' then 1 end) AS "Sales"
, sum(case when event_type = 'SALE' then amount end) AS "Sales Total"
, count(case when event_type = 'PURCHASE' then 1 end) AS "Purchases"
, sum(case when event_type = 'PURCHASE' then amount end) AS "Purchases Total"
FROM commercial_events ce
WHERE event_type IN ('PURCHASE', 'SALE')
  AND state = 'COMPLETED'
GROUP BY date_trunc(ce."hour",event_tstamp)
ORDER BY 1;

edited Dec 22, 2019 at 11:40

answered Dec 22, 2019 at 11:35

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

2 Comments

dlyk1988 Over a year ago

I just finished hunting the order/group miss-type, but you were faster than me. It works perfectly! Thanks.

LukStorms Over a year ago

Thx. Btw, you'll notice that the aliases now have double quotes. It's more following the SQL standard. You don't want a query to mistake a "field alias" for a 'string literal'.

ravioli · Accepted Answer · 2019-12-22 11:30:02Z

1

You can use a conditional SUM:

SELECT 
  date_trunc('hour',event_tstamp) AS 'Hour', 
  SUM(
    CASE WHEN event_type = 'PURCHASE' AND state = 'COMPLETED' THEN 1 ELSE 0 END
  ) AS 'Purchases', 
  SUM(
    CASE WHEN event_type = 'PURCHASE' AND state = 'COMPLETED' THEN amount ELSE 0 END
  ) AS 'Purchases_Total', 
  SUM(
    CASE WHEN event_type = 'SALE' AND state = 'COMPLETED' THEN 1 ELSE 0 END
  ) AS 'Sales', 
  SUM(
    CASE WHEN event_type = 'SALE' AND state = 'COMPLETED' THEN amount ELSE 0 END
  ) AS 'Sales_Total'
FROM commercial_events
GROUP BY 1;

This will only include the values in the SUM and COUNT calculations based on the values in the rows for event_type and state.

answered Dec 22, 2019 at 11:30

ravioli

3,8213 gold badges17 silver badges35 bronze badges

Collectives™ on Stack Overflow

Combine 2 SQL queries from the same table to single output

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related