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
Solved

MySQL totalling across multiple tables

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
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.
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…

839 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