?
Solved

MySQL totalling across multiple tables

Posted on 2013-11-14
5
Medium Priority
?
334 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 49

Accepted Solution

by:
PortletPaul earned 2000 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 49

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

752 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