I've been struggling with this query for two days now. I've got a user table with some values which has a relation with an order table (user can have multiple orders). This table has a relation with order_item (order can have multiple orderItems). Order_Item has a relation with invoice (order_item can have multiple invoices.
The branch and shop have a one-on-one relation with the user.
Here are the most important values of all the tables:
user: -userId (int) order -orderId (int) -userId (int) -inserted (date) order_item -orderItemId (int) -orderId (int) invoice -invoiceId (int) -orderItemId (int) -cost (double)
The foreign keys are self-explanatory here. User->Order->OrderItem->Invoice. What I need is a query in which each row in the result represents a user with two columns representing the total sales (sum of costs) in 2014 and 2015.
So what it has to do is show each user in a row with some info from the user table (company name, e-mail etc etc) and two columns with the total costs of 2014 and one of 2015 based on the order.inserted date value.
An example would be:
Name: | E-Mail | 2014 Cost | 2015 Cost
Google | [email protected] | €50.000 | €45.000
Now I've gotten so far that I've got a result for the first sum (showing all users regardless of cost), only when I join a second time (to calculate the 2015 cost) my previous sum costs get completely screwed up.
I tried some select queries within joins but I couldnt get any query to work. It's not like I'm a complete beginner in SQL but this is too complex for me to figure out this exact moment.
This is the query I use to get the 2014 result (and as soon as I add a second join for 2015 it gets screwed up):
SELECT t.userId, SUM(i.cost), t.companyName, t.email, t.website, t.tel, t.priority, b.name AS Branch, s.name AS `Shop Name` FROM `user` AS t LEFT JOIN branch AS b ON b.branchId = t.branchId LEFT JOIN shop AS s ON s.shopId = t.shopId LEFT JOIN `order` AS o ON (o.userId = t.userId AND YEAR(o.inserted) = 2014) LEFT JOIN order_item AS oi ON oi.orderId = o.orderId LEFT JOIN invoice AS i ON i.orderItemId = oi.orderItemId GROUP BY t.userId
I really hope somebody can help me with this. (I'm using mySQL/innoDB in Navicat 8).