Link to home
Create AccountLog in
Avatar of Peter Nordberg
Peter NordbergFlag for Sweden

asked on

Sum day, week, month and year

Hi,
I'm trying to write some queries to get report information from my webshop. I have two tables looking like this (just selected columns):
Order
orderID
orderDate

shopCart
shopCartID
orderID
price

What I would like to do is to be able to write queries that make me see how much has been sold per day, week, month and year. The output I'm after is something like this:
For day:
date               Sum
2008-03-07    5000
2008-03-06    4000
2008-03-05    5500
and so on...

For week:
date               Sum
v.5                5000
v.4                4000
v.3                5500
and so on...

For month:
date               Sum
March             5000
February        4000
January          5500
and so on...

For year:
date               Sum
2008               5000
2007               4000
2006               5500
and so on...

If someone could help me with this I would be very grateful.

Peter
ASKER CERTIFIED SOLUTION
Avatar of David Todd
David Todd
Flag of New Zealand image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of Peter Nordberg

ASKER

Thank you so much. Just what I was looking for. Is it also possible to get the week, month names instead of the dates?

Peter
You can add two tables with weekday names and month names and join in to translate the numbers, or you can add two functions like this:

http://codebetter.com/blogs/karlseguin/archive/2006/04/19/143090.aspx
Super!

Thanks!

Peter