Summarizing data by date in postgres

Question

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?

gotqn · Accepted Answer · 2020-04-29 10:08:18Z

1

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;

edited Apr 29, 2020 at 10:08

answered Apr 29, 2020 at 9:37

gotqn

44k47 gold badges167 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jonas Palačionis Over a year ago

Don't think it works, instead of percentages that would add up to a 100 per day I get such values for 2020-04-01 - `4.732211170619, 1.429396810723, 11.225045372050

gotqn Over a year ago

@JonasPalačionis I have change the code to give you the % share of each value across the total.

Collectives™ on Stack Overflow

Summarizing data by date in postgres

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related