Hopefully someone will be able to shed some light on this as I am stumped!
Basically I have a website were users enter deals into. I have two tables in my database:
One called "users" - This stores user information for example user ID username password etc.
Another table called "deals" - This stores deal information. Deal name, deal ID, user ID (which user did the deal), Deal amount, and Date the format for date is (YYYY-MM-DD).
I am now trying to display the total amount of deals done by each user for a current week for each day. For example in a 7 day calander type of style:
If you're wanting to do dynamically named columns (which is OK, and I typically do this when someone wants a "report" they could use in excel or whatever), you'll need to use at least two queries if you want to partition across a preset segment of time (like, tomorrow... your column headings would be different)... ie: your column headings are determined at runtime
you could alternatively do what you suggested, which is something like this:
u.uid as userID,
SUM(dealamount) as total,
SUM(IF(DAY(dealdate) = 'MONDAY', d.dealamount, 0)) as 'MONDAY',
SUM(IF(DAY(dealdate) = 'TUESDAY', d.dealamount, 0)) as 'TUESDAY',
SUM(IF(DAY(dealdate) = 'WEDNESDAY', d.dealamount, 0)) as 'WEDNESDAY',
SUM(IF(DAY(dealdate) = 'THURSDAY', d.dealamount, 0)) as 'THURSDAY',
SUM(IF(DAY(dealdate) = 'FRIDAY', d.dealamount, 0)) as 'FRIDAY',
SUM(IF(DAY(dealdate) = 'SATURDAY', d.dealamount, 0)) as 'SATURDAY',
SUM(IF(DAY(dealdate) = 'SUNDAY', d.dealamount, 0)) as 'SUNDAY',
FROM deals d
JOIN users u
ON u.uid = d.useriD
WHERE YEARWEEK( dealdate ) = YEARWEEK( CURRENT_DATE )
GROUP BY u.uid; //This part is important because it is a summary/aggregate
just a note about style of code, please refrain from doing this:
FROM deals d, users u
WHERE u.uid = d.userid
it might not matter at first, but when you start writing more advanced queries this will cause MySQL to create the product of both tables (INSANELY INEFFICIENT)... so if you have 1,000,000 users and 1,000,000 deals.... MySQL may try to create a tmp table with 1,000,000,000,000 records to handle this kind of query.... I say try because it will basically just waste hours and hours of computing time and not work.
You wrote a cross join (or more notoriously called "a common join") and while MySQL may optimize this simple query, since it's such a common `mistake`, you should really be writing it like this:
SELECT ... FROM deals INNER JOIN users ON (deads.userid = users.uid) ...
Just to recap:
FROM a JOIN b ON a.id=b.aid
is short hand for
FROM a INNER JOIN b ON a.id=b.aid
is short hand for
FROM a CROSS JOIN b
which is the cartesian product
That all being said, sometimes a comma/cross join is a very elegant way to solve a problem. It can be the perfect tool in certain circumstances.
Last edited by eval(BadCode); 03-20-2013 at 06:08 PM.
I use (, ; : -) as I please- instead of learning the English language specification: I decided to learn Scheme and Java;