CODEWITHSUNDEEP
postgresqlpivot
sparkle
sparkle

Reputation: 7400

Create a pivot table for Month over Month variation

I have these records returned from a query

+---------+--------------+-----------+----------+
| Country | other fields |   sales   |   date   |
+---------+--------------+-----------+----------+
| US      | 1            |  $100.00  | 01/01/21 |
| CA      | 1            |  $100.00  | 01/01/21 |
| UK      | 1            |  $100.00  | 01/01/21 |
| FR      | 1            |  $100.00  | 01/01/21 |
| US      | 1            |  $200.00  | 01/02/21 |
| CA      | 1            |  $200.00  | 01/02/21 |
| UK      | 1            |  $200.00  | 01/02/21 |
| FR      | 1            |  $200.00  | 01/02/21 |

And I want to show the sales variation from one month to previous, like this:

| Country | 01/02/21     | 01/01/21  | Var%     |
| US      |  $200.00     |  $100.00  | 100%     |
| CA      |  $200.00     |  $100.00  | 100%     |
| FR      |  $200.00     |  $100.00  | 100%     |
+---------+--------------+-----------+----------+

How could be done with a Postgres query?

Upvotes: 3

Views: 212

Answers (1)

eshirvana
eshirvana

Reputation: 24593

if you always comparing two month only :

select country
    , sum(sales) filter (where date ='01/01/21')  month1
    , sum(sales) filter (where date ='01/02/21')  month2
    , ((sum(sales) filter (where date ='01/02/21') /sum(sales) filter (where date ='01/01/21')) - 1) * 100 var
from tablename
where date in ('01/01/21' , '01/02/21')
group by country

you also can look at crosstab from tablefunc extension which basically does the same as above query.

CREATE EXTENSION IF NOT EXISTS tablefunc;

select * ,("01/02/21" /"01/01/21") - 1) * 100 var 
from(
select * from crosstab ('select Country,date , sales from tablename')
as ct(country varchar(2),"01/01/21" money , "01/02/21" money)
) t

for more info about crosstab , see tablefunc

but if you want to show date in rows instead of columns, you can easily generalize it for all the dates :

select * 
   , ((sales / LAG(sales,1,1) over (partition by country order by date)) -1)* 100  var
from 
country

Upvotes: 4

Related Questions