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?

 

[ceco]

try removing to_char

group by to_char(date, 'YYYY Month DD')

just leave it

GROUP BY date

(mysql is not very strict with GROUP BY it cat be very convinient sometimes, but also very unpredictable)

 

[olav78]

Thank you. But I need to group sales by day (not by millisecond . I figured it out by the way:

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')