Canman2005 Posted August 28, 2007 Share Posted August 28, 2007 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 Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted August 28, 2007 Author Share Posted August 28, 2007 Does anyone need anymore info to help me or is this all ok? Quote Link to comment Share on other sites More sharing options...
hostfreak Posted August 28, 2007 Share Posted August 28, 2007 What exactly is the problem? Quote Link to comment Share on other sites More sharing options...
Jessica Posted August 28, 2007 Share Posted August 28, 2007 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. Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted August 28, 2007 Author Share Posted August 28, 2007 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 Quote Link to comment Share on other sites More sharing options...
Canman2005 Posted August 28, 2007 Author Share Posted August 28, 2007 Any ideas? Am I not explaining it right? Quote Link to comment Share on other sites More sharing options...
Jessica Posted August 28, 2007 Share Posted August 28, 2007 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.