Peter Nordberg

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

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

membership

Create an account to see this answer

Signing up is free. No credit card required.

SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

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

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

ASKER

Super!

Thanks!

Peter

Thanks!

Peter

ASKER

Peter