CODEWITHSUNDEEP
sqlpostgresql
Piotr
Piotr

Reputation: 13

SQL / PostgreSQL How to do a query that merge values from some columns into one row

There is a table:

[region] [country] [month] [VAL] [VAL_PY]
EU Poland January x a
EU Poland January x1 a2
EU Poland February xx aa
EU France January y b
NA Brazil February z c
... ... ... ... ...

there is a need to make another table based on the one above, such as the one below, one row for one country, 12 new columns = 12 months, the value in each country/month is the KPI:

[region] [country] [January] February] ... [December]
EU Poland KPI KPI ... KPI
EU France KPI KPI ... KPI
... ... KPI KPI ... KPI

The KPI code:

CASE WHEN  sum(A."value_py")=0 THEN 0 
else round(((sum("value")-sum("value_py"))/sum("value"))*100,1 
END

Please help me, I can find the solution where there are duplicates in every country which is not acceptable

Upvotes: 1

Views: 55

Answers (1)

The Impaler
The Impaler

Reputation: 48865

You are pivoting the data. You can use FILTER to retrieve data for each specific column.

For example:

select
  region,
  country,
  case when sum(a.val_py) filter(where month = 'January') = 0 then 0 
       else round(((sum(val) filter(where month = 'January') - 
            sum(val_py) filter(where month = 'January')) / 
            sum(val_py) filter(where month = 'January')) * 100, 1) 
  end as january,
  case when sum(a.val_py) filter(where month = 'February') = 0 then 0 
       else round(((sum(val) filter(where month = 'February') - 
            sum(val_py) filter(where month = 'February')) / 
            sum(val_py) filter(where month = 'February')) * 100, 1) 
  end as february,
  -- add the rest of the months here...
from t
group by region, country

Upvotes: 1

Related Questions