Sum/aggregate data based on dates

Question

I have the following table about the items sold out in every shop.

table columns

shop_id: the specific id of the shop,
sold: the purchase amount as dollars ($)
time: the date and time of the purchase.

data

shop_id sold    time
1       12.44   23/10/2014 20:20
1       12.77   24/10/2014 20:18
1       10.72   24/10/2014 20:18
1       14.51   24/10/2014 20:18
2       5.94    22/10/2014 20:11
2       15.69   23/10/2014 20:23
2       8.55    24/10/2014 20:12
2       6.96    24/10/2014 20:18
3       8.84    22/10/2014 20:21
3       7.82    22/10/2014 20:21
3       22.19   23/10/2014 20:23
3       13.21   23/10/2014 20:23
4       14.60   23/10/2014 20:20
4       12.19   23/10/2014 20:23
4       5.41    24/10/2014 20:18
4       10.93   24/10/2014 20:19
5       18.54   22/10/2014 20:21
5       7.48    22/10/2014 20:21
5       10.67   24/10/2014 20:18
5       15.96   24/10/2014 20:18

I have 3 classifiers per purchase :

purchase classifiers

low: 0-8 $
medium: 8-12 $
high: 12 and higher $

What I would like to do is to write a query using PostgreSQL that will produce the total purchase each day by each type of purchase classifier.

desired output

date        low     medium  high
22/10/2014  29.10   14.51   12.77
23/10/2014  0       0       70.06
24/10/2014  16.34   51.24   41.39

Thank you very much in advance for your kind help. I am not experienced with postgreSQL. I would do this in R easily, but since moving a huge database table to R is very cumbersome, I need to learn some postgreSQL.

use case statements to put the amount sold into one of three fields low|medium|high, then sum sold grouping by date, low, medium and high — amdixon
– amdixon, Commented Jul 5, 2015 at 14:23
Thanks a lot for your suggestion. Since this is a very new field to me I needed the full code. The answer below from @jpw is what I needed. — Ali Altıntaş
– Ali Altıntaş, Commented Jul 5, 2015 at 22:27

jpw · Accepted Answer · 2015-07-05 14:33:58Z

3

I believe you can do this using conditional aggregation like this:

select 
  cast(time as date),
  sum(case when sold > 0 and sold <= 8 then sold else 0 end) as low,
  sum(case when sold > 8 and sold <= 12 then sold else 0 end) as medium,
  sum(case when sold > 12 then sold else 0 end) as high
from your_table
group by cast(time as date);

You might have to tweak the ranges a bit - should 8 fall into both low and medium? Also, I can't remember if cast("time" as date) is the correct syntax for Postgresql - I suspect it might not be, but just replace that part with the correct function to get the date from the datetime/timestamp column. It should probably be "time"::date

edited Jul 5, 2015 at 14:33

answered Jul 5, 2015 at 14:27

jpw

45k6 gold badges71 silver badges90 bronze badges

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

1 Comment

Ali Altıntaş Over a year ago

Thank you so much for your very kind help and editing the format of my question! This is exactly what I needed.

Collectives™ on Stack Overflow

Sum/aggregate data based on dates

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related