CODEWITHSUNDEEP
postgresql
Jonas Palačionis
Jonas Palačionis

Reputation: 4842

Summarizing data by date in postgres

I've written an query which gives back table like this, ( sales are not a number, but a transaction ID, so I count the occurrences of the IDs ):

select datestamp, product, count(sales) from table where datestamp > '2020-03-31'
and product <> ''
group by datestamp, product
order by datestamp

datestamp   product     sales

2020-04-01  product_1   4327
2020-04-01  product_2   1307
2020-04-01  product_3   551
2020-04-02  product_1   13106
2020-04-02  product_2   1169
2020-04-02  product_3   342
2020-04-03  product_1   178
2020-04-03  product_2   511
2020-04-03  product_3   416
2020-04-04  product_1   186
2020-04-04  product_2   127
2020-04-04  product_3   134

I want to sum all of the sales by datestamp and divide them by each sale of product so that I would get a percentage of how many sales per day are in each product category:

datestamp   product     sales    percentage

2020-04-01  product_1   4327     = (4327 + 1307 + 551) / 4327
2020-04-01  product_2   1307     = (4327 + 1307 + 551) / 1307
2020-04-01  product_3   551      = (4327 + 1307 + 551) / 551
2020-04-02  product_1   13106    ...
2020-04-02  product_2   1169
2020-04-02  product_3   342
2020-04-03  product_1   178
2020-04-03  product_2   511
2020-04-03  product_3   416
2020-04-04  product_1   186
2020-04-04  product_2   127
2020-04-04  product_3   134

How can I achieve such a result?

Upvotes: 1

Views: 40

Answers (1)

gotqn
gotqn

Reputation: 43636

Try this:

DROP TABLE IF EXISTS DataSource;

CREATE TEMPORARY TABLE DataSource
(
    datestamp   DATE
   ,product VARCHAR(12)
   ,sales INT 
);

INSERT INTO DataSource (datestamp, product, sales)
VALUES ('2020-04-01', 'product_1',4327)
,('2020-04-01', 'product_2',1307)
,('2020-04-01', 'product_3',551)
,('2020-04-02', 'product_1',13106)
,('2020-04-02', 'product_2',1169)
,('2020-04-02', 'product_3',342)
,('2020-04-03', 'product_1',178)
,('2020-04-03', 'product_2',511)
,('2020-04-03', 'product_3',416)
,('2020-04-04', 'product_1',186)
,('2020-04-04', 'product_2',127)
,('2020-04-04', 'product_3',134);

select datestamp, product, sales, (sales * 1.0 / sum(sales) over (partition by datestamp)  )  * 100.0
from DataSource
order by datestamp;

Upvotes: 1

Related Questions