Combine Postgres query results into one table

Question

I have two Postgres queries that work.

SELECT date_trunc('hour', time) t, COUNT(*) AS "SC15" FROM logfile
      WHERE source = 'SC15' AND type = 'Sort'
      GROUP BY t ORDER BY t;

SELECT date_trunc('hour', time) t, COUNT(*) AS "SC71" FROM logfile
      WHERE source = 'SC71' AND type = 'Sort'
      GROUP BY t ORDER BY t;

How can I get the results to display side-by-side in separate columns? A t (interval) column, an SC15 column, and an SC17 column grouped by hour. I have tried UNION and INTERSECT. I think I need to make a temp table? Not sure how. Thanks!

S-Man · Accepted Answer · 2018-10-03 16:24:33Z

2

There is no need to do this in two queries

SELECT 
    date_trunc('hour', time) t,
    COUNT(*) FILTER (WHERE source = 'SC15') AS "SC15", 
    COUNT(*) FILTER (WHERE source = 'SC71') AS "SC71" 
FROM logfile
WHERE source IN ('SC15', 'SC71') AND type = 'Sort'
GROUP BY t
ORDER BY t

The IN operator allows you to filter more than one value. The FILTER clause filters out some rows before counting.

edited Oct 3, 2018 at 16:24

answered Oct 3, 2018 at 16:19

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

Comments

rgen3 · Accepted Answer · 2018-10-03 16:30:08Z

You can use CTE with window function row_number() and after join the results. Note it is better to use left join, because you may have different rows count in CTE

with l1 as (
SELECT
  row_number() over(order by t) rn,
  date_trunc('hour', time) t,
  COUNT(*) c
FROM logfile l1
WHERE source = 'SC15' AND type = 'Sort'
GROUP BY t
ORDER BY t
),
l2 as (
SELECT 
  row_number() over(order by t) rn,
  date_trunc('hour', time) t, 
  COUNT(*) c
FROM logfile
WHERE source = 'SC71' AND type = 'Sort'
GROUP BY t
ORDER BY t
) SELECT l1.t, l1.c, l2.t, l2.c
FROM l1
LEFT JOIN l2 on l1.rn=l2.rn

as one more variant you can try something like this

select 
date_trunc('hour', time) t,
count(*) over (partition by date_trunc('hour', time), source, type) c
from logfile
where source in ('SC15', 'SC71') AND type = 'Sort'

Collectives™ on Stack Overflow

Combine Postgres query results into one table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related