Solved

MySQL totalling across multiple tables

Posted on 2013-11-14
5
324 Views
Last Modified: 2013-11-15
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

0
Comment
Question by:kbit
  • 3
  • 2
5 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39647765
The effects of joining can multiply the results, if you replaced line 1 to 6 with "select *" you can see the figures repeated several times - which is not conducive to accurate summation. So, do the summation as subqueries to eradicate the repetition:
SELECT
      projects.pcode AS 'Job No'
    , clients.NAME
    , ifnull(fi.Invoiced, 0) AS Invoiced
    , ifnull(ti.Time_Cost, 0) AS 'Time Cost'
FROM clients
INNER JOIN projects ON clients.code = projects.client
LEFT JOIN (
    SELECT
          project
        , client
        , 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'
    FROM financitems
    GROUP BY
          project
        , client
    ) AS fi ON projects.code = fi.project
    AND projects.client = fi.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
;

| JOB NO |      NAME | INVOICED | TIME COST |
|--------|-----------|----------|-----------|
|   6046 | MyCompany |     8085 |       225 |
|   6047 | MyCompany |        0 |         0 |



CREATE TABLE clients
	(`Code` int, `Name` varchar(9))
;
	
INSERT INTO clients
	(`Code`, `Name`)
VALUES
	(533, 'MyCompany')
;

CREATE TABLE financitems
	(`Code` int, `Type` varchar(10), `Client` int, `Project` int, `Amount` int)
;
	
INSERT INTO financitems
	(`Code`, `Type`, `Client`, `Project`, `Amount`)
VALUES
	(1, 'INVOICE', 533, 2201, 11986.66),
	(2, 'CREDIT INV', 533, 2201, 3901.66)
;

CREATE TABLE timeitems
	(`Code` int, `JobCost` int, `Client` int, `Project` int)
;
	
INSERT INTO timeitems
	(`Code`, `JobCost`, `Client`, `Project`)
VALUES
	(1, 100.00, 533, 2201),
	(2, 50.00, 533, 2201),
	(3, 75.00, 533, 2201)
;

CREATE TABLE projects
	(`Code` int, `Pcode` int, `Client` int)
;
	
INSERT INTO projects
	(`Code`, `Pcode`, `Client`)
VALUES
	(2201, 6046, 533),
	(2284, 6047, 533)
;

http://sqlfiddle.com/#!9/feed0/7

Open in new window

0
 

Author Comment

by:kbit
ID: 39647785
Wow thanks ever so much for such a tremendous answer, I've got a lot to learn.
0
 

Author Closing Comment

by:kbit
ID: 39647788
Wow thanks ever so much for such a tremendous answer, I've got a lot to learn.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39649754
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.
0
 

Author Comment

by:kbit
ID: 39650518
Thanks again. I've already added more tables to it and it still works perfectly.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now