Solved

MySQL totalling across multiple tables

Posted on 2013-11-14
5
327 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

803 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