CODEWITHSUNDEEP
sqlpostgresql
Ali Altıntaş
Ali Altıntaş

Reputation: 305

Sum/aggregate data based on dates

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

table columns

  1. shop_id: the specific id of the shop,
  2. sold: the purchase amount as dollars ($)
  3. 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

  1. low: 0-8 $
  2. medium: 8-12 $
  3. 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.

Upvotes: 2

Views: 917

Answers (1)

jpw
jpw

Reputation: 44931

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

Upvotes: 3

Related Questions