Counting query

[Canman2005]

Hi all

 

I have a small online shop, i have 2 SQL tables, one called "orderlog" which stores each item the user has purchased and also a table called "ordertransactions" which store the master details of each order. Each row of the table "ordertransactions" has a unique ID number.

 

An example is

 

<< orderlog >>

 

ID    ORDERID  TOTAL(£)

1      876          29.99

2      876          13.99

3      899          10.00

 

ID - this is a autoincreased number

ORDERID - this is the ID number for the master order details in the table `ordertransactions`

TOTAL - this is the total for each product bought

 

<< ordertransactions >>

 

ID    DATE

876  2007-08-12

899  2007-08-14

 

ID - this is a autoincreased number, this number is what is held in the field `ORDERID` in the `orderlog` table.

DATE - this is the date the order was placed, in the format YYYY-MM-DD.

 

As you can see, in the table `ordertransactions` there are 2 orders held, in the table `orderlog` you can see there are 3 rows, the first 2 rows relate to order ID 876 and the last row relates to order ID 899.

 

You will now notice that inside the table `ordertransactions` there is a date held in the format YYYY-MM-DD.

 

Okay, so what I want to do is to run a SUM query

 

SELECT SUM(price) FROM ***

 

on the `ordertransactions` table to return the total earned in a particular month. So if I run the query for the month August (08) then it would check the table `ordertransactions" for all rows where the month value equals 08, it would then grab the ID numbers for each of these rows and do a SUM on the `orderlog` table for all rows which contain those ORDER ID numbers, this would then return the TOTAL spent during the month 08.

 

Does that make sense?

 

Can anyone help, i've been trying to crack this for the last 9 hours and im getting no where fast.

 

Thanks in advance

 

Dave

[Canman2005]

Does anyone need anymore info to help me or is this all ok?

[hostfreak]

What exactly is the problem?

[Jessica]

I think you'll need to use the MYSQL date and time functions - which one I am not sure, but check out the manual:

http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html

 

You'll need to do a SUM where your date field is reformatted with one of these functions.

[Canman2005]

Hi

 

I dont think I explained it very well.

 

Basically I want to get the ID number of all orders held in the "ordertransactions" table which were placed in August, I then want add together all the TOTAL fields in the 'orderlog' table which have the ORDER ID number of any of those orders placed in August on the `ordertransactions` table.

 

On my example data, it would look in the table

 

<< ordertransactions >>

 

and get the following rows (as they are the month of August)

 

ID    DATE

876  2007-08-12

899  2007-08-14

 

It would store the ID numbers and then get all the rows in the table `orderlog` which have those ID numbers stored in the ORDERID field;

 

ID    ORDERID  TOTAL(£)

1      876          29.99

2      876          13.99

3      899          10.00

 

It would then add up all the totals for those ID's and then output it the TOTAL added together.

 

So it would get ID numbers `876` and `899` and look them up in the ORDERID field of the `orderlog` table, which are ID numbers 1,2 and 3 and then add up the total for the TOTAL field, outputting 29.99 + 13.99 + 10.00.

 

Does that make much sense?

 

Thanks in advance

 

Dave

[Canman2005]

Any ideas?

 

Am I not explaining it right?

[Jessica]

You want to get all the transactions from a specific month, but you have them stored with the date in the format YYYY-MM-DD or something like that. You need to use a date formatting function to get the ones you want.