By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

Sum of “sales” for each date in a timestamp column

P: 9

I have a table named sales with the columns date (timestamp) and price (bigdecimal or something). I want to get the sale for each day (the sum of ‘price’ for all the rows with the same ‘date’). Like:
2006-10-10, 17 sales, 1500.00 $
2006-10-11, 15 sales, 1400.00$

This is how I try to do it:
SELECT to_char(date, 'YYYY'), to_char(date, 'Month'), to_char(date, 'DD'), to_char(date, 'Day'), sum(price)
FROM sales
WHERE hotel_id = 1 and date > 2006-10-09
group by to_char(date, 'YYYY Month DD')
order by date

But I get an exception telling me that date has to be in the ‘group by’ clause… It works in MySQL (substituting to_char with date_format), but postgreSQL is so much cooler, and therefore: anyone knows a solution to this?
Dec 12 '06 #1
Share this Question
Share on Google+
3 Replies

Expert 100+
P: 534
you're practically there, try this:

Expand|Select|Wrap|Line Numbers
  1. SELECT to_char(date, 'YYYY Month DD Day'), sum(price) FROM sales
  2. WHERE hotel_id = 1 and date > '2006-10-09'
  3. GROUP by date
  4. ORDER by date
Dec 12 '06 #2

Expert 100+
P: 534
Something made me look back at my posting and I noticed that my sql may need to be corrected. Perhaps you already did that.
If your date column is the timestamp (has hours, mins, seconds)
you really need to group by formatted date, not by the column value, and you have to sort by the same formatted value to avoid the original error you were getting.
Expand|Select|Wrap|Line Numbers
  1. SELECT to_char(date, 'YYYY Month DD Day'), sum(price) FROM sales
  2. WHERE hotel_id = 1 and date > '2006-10-09'
  3. GROUP by to_char(date, 'YYYY Month DD Day')
  4. ORDER by to_char(date, 'YYYY Month DD Day')
If you need to know the number of sales per day add "count(date)" to the select list. This works on PostgreSQL 7.3
Hope it helps.
Dec 13 '06 #3

P: 9
You are right. I figured it out myself actually, but thanks anyway.

I did it this way, a bit longer, but in principal almost the same:

Expand|Select|Wrap|Line Numbers
  1. SELECT to_char(date, 'YYYY') to_char(date 'Month'), to_char(date, 'DD'), to_char (date, 'Day'), sum(price)
  2. FROM sales
  3. WHERE hotel_id = 1 and date > '2006-10-09'
  4. GROUP by to_char(date, 'YYYY') to_char(date 'Month'), to_char(date, 'DD'), to_char (date, 'Day')
  5. ORDER by to_char(date, 'YYYY')
(i need year, month and day in separate strings later anyway ;)
Dec 14 '06 #4

Post your reply

Sign in to post your reply or Sign up for a free account.