Solved

MySQL totalling across multiple tables

Posted on 2013-11-14
5
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MySQL limit and not so limited 13 60
Why is my $_POST not going to results page 10 68
T-SQL: Need Group By to use "fuzzy logic"?? 3 51
WordPress  Failed to Import Media 8 64
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

732 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