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

[olav78]

Hi!

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$
etc…

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?

[michaelb]

you're practically there, try this:

SELECT to_char(date, 'YYYY Month DD Day'), sum(price) FROM sales
WHERE hotel_id = 1 and date > '2006-10-09'
GROUP by date
ORDER by date

[michaelb]

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.

SELECT to_char(date, 'YYYY Month DD Day'), sum(price) FROM sales
WHERE hotel_id = 1 and date > '2006-10-09'
GROUP by to_char(date, 'YYYY Month DD Day')
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.

[olav78]

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:

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') to_char(date 'Month'), to_char(date, 'DD'), to_char (date, 'Day')
ORDER by to_char(date, 'YYYY')
 

(i need year, month and day in separate strings later anyway ;)