I have data in a PostgreSQL database in the following format:
unique_key category date_period value
So for example.
agriculture_all agriculture 2021 15
agriculture_partial agriculture 2021 10
science_all science 2021 83
science_partial science 2021 32
The query I want to run is to GROUP BY based on category and date_period, but then to display the values next to each other.
**[query here]**
output:
category date_period all partial
agriculture 2021 15 10
science 2021 83 32
It feels like a really simple request but I'm struggling to get it working right in SQL. Any advice appreciated.
In PostgreSQL you can use simple query using sum with filter:
select
category,
sum(value) filter (where unique_key like '%_all') all,
sum(value) filter (where unique_key like '%_partial') partial
from tbl
group by category;
For prevent null values coalesce function may by applied:
select
category,
coalesce(sum(value) filter (where unique_key like '%_all'), 0) all,
coalesce(sum(value) filter (where unique_key like '%_partial'), 0) partial
from tbl
group by category;
You could use a common table expression...
with partial as
(select category, date_period, value
from t1
where unique_key ilike '%partial%'
)
select a.category,
a.date_period,
a.value as all,
coalesce(p.value, 0) as partial
from t1 a
left join partial p
on p.category = a.category
and p.date_period = a.date_period
where a.unique_key ilike '%all%'
db-fiddle here
A bit of conditional aggregation will do the trick.
SELECT
t.category
, t.date_period
, SUM(CASE WHEN t.unique_key LIKE '%\_all' THEN t.value ELSE 0 END) AS "all"
, SUM(CASE WHEN t.unique_key LIKE '%\_partial' THEN t.value ELSE 0 END) AS "partial"
FROM your_table t
GROUP BY t.category, t.date_period
ORDER BY t.category, t.date_period
category | date_period | all | partial :---------- | ----------: | --: | ------: agriculture | 2021 | 15 | 10 science | 2021 | 83 | 32
db<>fiddle here