Avatar of kbit
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.....Amount
1...........INVOICE.........533........2201........11986.66
2...........CREDIT INV....533.........2201........3901.66

timeitems
Code.....JobCost.....Client.....Project
1...........100.00......533........2201
2...........50.00........533........2201
3...........75.00........533........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...............Invoiced..........Time Cost
6046.....MyCompany.....8085.00...........225.00
6047.....MyCompany.....0.00.................0.00

Instead, using the query below, my result is:
Job No...Name...............Invoiced..........Time Cost
6046.....MyCompany.....24255.00.........450.00
6047.....MyCompany.....0.00.................0.00


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

Open in new window

MySQL ServerSQL

Avatar of undefined
Last Comment
kbit

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
kbit

Wow thanks ever so much for such a tremendous answer, I've got a lot to learn.
ASKER
kbit

Wow thanks ever so much for such a tremendous answer, I've got a lot to learn.
PortletPaul

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.
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
;

Open in new window

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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ASKER
kbit

Thanks again. I've already added more tables to it and it still works perfectly.