×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

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

(OP)
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?

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

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)

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close