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.

CodePudding user response：

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;

PostgreSQL sum filter fiddle

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;

PostgreSQL coalesce filter

CodePudding user response：

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

CodePudding user response：

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