kbit
asked on
MySQL totalling across multiple tables
Hi,
I have some tables as follows:
projects
Code.....Pcode....Client
2201.....6046......533
2284.....6047......533
clients
Code.....Name
533.......MyCompany
financitems
Code.....Type............. ..Client.. ...Project .....Amoun t
1...........INVOICE....... ..533..... ...2201... .....11986 .66
2...........CREDIT INV....533.........2201... .....3901. 66
timeitems
Code.....JobCost.....Clien t.....Proj ect
1...........100.00......53 3........2 201
2...........50.00........5 33........ 2201
3...........75.00........5 33........ 2201
What I'm trying to do is that for each project on the "projects" table, join to the "clients" table to retrieve the name. Every project will be linked to a client, which is why I'm using an INNER join.
Not every project may have a record on the "financitems" and "timeitems" tables, which is why I'm using a LEFT join.
For matched records, I'm trying to SUM the "Amount" (by subtracting the credits from the invoices) on the "financitems" table and SUM the "JobCost" on the "timeitems" table.
The end result should be:
Job No...Name...............In voiced.... ......Time Cost
6046.....MyCompany.....808 5.00...... .....225.0 0
6047.....MyCompany.....0.0 0......... ........0. 00
Instead, using the query below, my result is:
Job No...Name...............In voiced.... ......Time Cost
6046.....MyCompany.....242 55.00..... ....450.00
6047.....MyCompany.....0.0 0......... ........0. 00
Can someone please advise on where I'm going wrong and correct the SQL?
Many thanks
I have some tables as follows:
projects
Code.....Pcode....Client
2201.....6046......533
2284.....6047......533
clients
Code.....Name
533.......MyCompany
financitems
Code.....Type.............
1...........INVOICE.......
2...........CREDIT INV....533.........2201...
timeitems
Code.....JobCost.....Clien
1...........100.00......53
2...........50.00........5
3...........75.00........5
What I'm trying to do is that for each project on the "projects" table, join to the "clients" table to retrieve the name. Every project will be linked to a client, which is why I'm using an INNER join.
Not every project may have a record on the "financitems" and "timeitems" tables, which is why I'm using a LEFT join.
For matched records, I'm trying to SUM the "Amount" (by subtracting the credits from the invoices) on the "financitems" table and SUM the "JobCost" on the "timeitems" table.
The end result should be:
Job No...Name...............In
6046.....MyCompany.....808
6047.....MyCompany.....0.0
Instead, using the query below, my result is:
Job No...Name...............In
6046.....MyCompany.....242
6047.....MyCompany.....0.0
Can someone please advise on where I'm going wrong and correct the SQL?
Many thanks
SELECT
projects.pcode AS 'Job No',
clients.Name,
ROUND(SUM(CASE WHEN financitems.type='INVOICE' THEN financitems.amount ELSE 0 END) - SUM(CASE WHEN financitems.type='CREDIT INV' THEN financitems.amount ELSE 0 END),2) AS 'Invoiced',
SUM(CASE WHEN timeitems.jobcost>0 THEN timeitems.jobcost ELSE 0 END) AS 'Time Cost'
FROM clients
INNER JOIN projects ON clients.code=projects.client
LEFT JOIN financitems ON projects.code=financitems.project AND projects.client=financitems.client
LEFT JOIN timeitems ON projects.code=timeitems.project AND projects.client=timeitems.client
WHERE clients.code=533
GROUP BY projects.code
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Wow thanks ever so much for such a tremendous answer, I've got a lot to learn.
HI, thanks for the grading. I did make a mistake in my code:
It is not necessary to have the final group by (lines 43-45) in that approach.
sorry
There are actually 3 options for this query. The one I have already supplied, plus:
aggregate financitems in a subquery; then aggregate as in your original - OR
aggregate timeitems in a subquery; then aggregate as in your original
e.g.
{+ edit}
Why did I offer the "two subquery" approach?
No particular reason, but if you add more tables into the query it will still work reliably.
It is not necessary to have the final group by (lines 43-45) in that approach.
sorry
There are actually 3 options for this query. The one I have already supplied, plus:
aggregate financitems in a subquery; then aggregate as in your original - OR
aggregate timeitems in a subquery; then aggregate as in your original
e.g.
SELECT
projects.pcode AS 'Job No'
, clients.NAME
, ROUND(SUM(CASE
WHEN financitems.type = 'INVOICE'
THEN financitems.amount
ELSE 0
END) - SUM(CASE
WHEN financitems.type = 'CREDIT INV'
THEN financitems.amount
ELSE 0
END), 2) AS 'Invoiced'
, ifnull(ti.Time_Cost, 0) AS 'Time Cost'
FROM clients
INNER JOIN projects ON clients.code = projects.client
LEFT JOIN financitems ON projects.code = financitems.project
AND projects.client = financitems.client
LEFT JOIN (
SELECT project
, client
, SUM(CASE
WHEN timeitems.jobcost > 0
THEN timeitems.jobcost
ELSE 0
END) AS 'Time_Cost'
FROM timeitems
GROUP BY project
, client
) AS ti ON projects.code = ti.project
AND projects.client = ti.client
WHERE clients.code = 533
GROUP BY
projects.pcode
, clients.NAME
, ti.Time_Cost
;
Note the final group by. MySQL is quite "relaxed" about the details of this query clause but it should contain all fields that are not being aggregated.{+ edit}
Why did I offer the "two subquery" approach?
No particular reason, but if you add more tables into the query it will still work reliably.
ASKER
Thanks again. I've already added more tables to it and it still works perfectly.
ASKER