?
Solved

Query Syntax - Max, Sum

Posted on 2014-08-11
6
Medium Priority
?
271 Views
Last Modified: 2014-08-11
SELECT s.sctype, MAX(o.JTDCOST) Cost_JTD
FROM jcost o
INNER JOIN sccode s ON s.SCCODE = o.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE o.JOB_ID = 7588            
AND o.DELETED = 'N'
AND o.WEDATE <= '2014-08-03'
GROUP BY o.SCCODE;

The above query returns 5 values that add up to 185,366.60
  13,683.75
  24,891.28
  38,862.48
107,562.82
       366.27

When it is incorporated in the query below the column returns 430,251.28

SELECT SUM(CostJTD)
FROM ccode c
LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
LEFT JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
LEFT JOIN job j ON c.JOB_ID = j.JOB_ID
      LEFT JOIN (SELECT s.SCTYPE COST, MAX(o.JTDCOST) CostJTD
      FROM jcost o
      LEFT JOIN sccode s ON s.SCCODE = o.SCCODE
      LEFT JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
      WHERE o.JOB_ID = 7588            
      AND o.DELETED = 'N'
      AND o.WEDATE <= '2014-08-03'
      GROUP BY s.SCTYPE) sub_query2 ON s.SCTYPE = sub_query2.COST
WHERE j.JOB_ID = 7588
AND c.DELETED = 'N'
AND c.POPU > 0
AND s.SCTYPE NOT IN(8,9,14)
GROUP BY s.SCTYPE;

it should be the 185,366.60
0
Comment
Question by:hdcowboyaz
  • 4
6 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 40254726
Without knowing your data, what jumps out immediately is your grouping is different in the two queries. If your second query is supposed to join to the exact same subquery that we see in your first query, you're grouping them differently.

First query is grouped by SCCODE. The second is grouped by SCTYPE. Without knowing your data, anything beyond this is a bit of a guess.
0
 

Author Comment

by:hdcowboyaz
ID: 40254732
When I modify it to the below query I get 741,466.40

SELECT SUM(CostJTD)
FROM ccode c
LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
LEFT JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
LEFT JOIN job j ON c.JOB_ID = j.JOB_ID
      LEFT JOIN (SELECT s.SCTYPE COST, MAX(o.JTDCOST) CostJTD
      FROM jcost o
      LEFT JOIN sccode s ON s.SCCODE = o.SCCODE
      LEFT JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
      WHERE o.JOB_ID = 7588            
      AND o.DELETED = 'N'
      AND o.WEDATE <= '2014-08-03'
      GROUP BY o.SCCODE) sub_query2 ON s.SCTYPE = sub_query2.COST
WHERE j.JOB_ID = 7588
AND c.DELETED = 'N'
AND c.POPU > 0
AND s.SCTYPE NOT IN(8,9,14)
GROUP BY s.SCTYPE;
0
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40254845
Why don't you just use:
SELECT SUM(Cost_JTD) FROM(
SELECT s.sctype, MAX(o.JTDCOST) Cost_JTD
FROM jcost o
INNER JOIN sccode s ON s.SCCODE = o.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE o.JOB_ID = 7588            
AND o.DELETED = 'N'
AND o.WEDATE <= '2014-08-03'
GROUP BY o.SCCODE)

Open in new window

0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:hdcowboyaz
ID: 40254862
310,045.40
0
 

Author Comment

by:hdcowboyaz
ID: 40254868
I just need to SUM this query...

SELECT MAX(o.JTDCOST) CostJTD
FROM jcost o
INNER JOIN sccode s ON s.SCCODE = o.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE o.JOB_ID = 7588            
AND o.DELETED = 'N'
AND o.WEDATE <= '2014-08-03'
GROUP BY o.SCCODE;
0
 

Author Closing Comment

by:hdcowboyaz
ID: 40254960
Got it....
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
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
Course of the Month15 days, 23 hours left to enroll

850 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