CODEWITHSUNDEEP
sqlpostgresql
Tuffgong
Tuffgong

Reputation: 57

Combine Postgres query results into one table

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!

Upvotes: 0

Views: 678

Answers (2)

rgen3
rgen3

Reputation: 917

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'

Upvotes: 1

S-Man
S-Man

Reputation: 23766

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.

Upvotes: 2

Related Questions