Query Syntax - Max, Sum

The query below returns an incorrect value for the column 'Cost JTD' (line 12). It returns two rows. The first row the column is 430,251 and it should be 185,367. The second row returns the correct value for that column since it only has one value. The first row has several data points that are added and obviously the wrong ones are grouped in the SUM. Look for /* this it the query in question */ in the below query

#Field Production Report (MATERIALS, ORIGINAL ESTIMATE/AS BID, JOB TO DATE, COST PROJECTION)
/*01*/SELECT
/*02*/t.SCTYPE 'Desc',
/*03*/t.UM 'UoM',
/*04*/FORMAT(SUM(c.POPU), 0) 'Orig Qty',
/*05*/IF(t.UM = 'LS', 1, ROUND(sf_PcacAvgPR(c.JOB_ID, t.SCTYPE), 0)) 'Orig PR',
/*06*/c.POEST 'Orig HR',
/*07*/FORMAT(SUM(c.POPU / c.POUEST), 0) 'Total Hours',
/*08*/FORMAT(SUM(((c.POPU / c.POUEST) * c.POEST)), 0) 'Est Cost',
/*09*/ROUND(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU), 4) 'Est Unit Cost',
/*10*/FORMAT(Placed_JTD, 0) 'Placed JTD',
/*11*/FORMAT(Hours_JTD, 0) 'Hours_JTD',
/*12*/CONCAT('$', FORMAT(SUM(CostJTD), 0)) 'Cost JTD',
/*13*/IF(t.UM = 'LS', 1, FORMAT(Placed_JTD/Hours_JTD, 2)) 'Actual PR',
/*14*/FORMAT(CostJTD/Placed_JTD, 4) 'Unit Cost JTD',      
/*15*/CONCAT('$', FORMAT((c.POEST / c.POUEST) * Placed_JTD, 0)) 'Est Cost',      
/*16*/CONCAT('$', FORMAT((c.POEST / c.POUEST) * Placed_JTD - SUM(CostJTD), 0)) 'Variance',
/*17*/FORMAT(SUM(CostJTD)/Hours_JTD, 2) 'Avg HR'
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
JOIN job j ON c.JOB_ID = j.JOB_ID
LEFT JOIN (SELECT s.SCTYPE PLACED, SUM(m.PLACEMENT) Placed_JTD
   FROM materials m
   JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
   JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN job j ON m.JOB_ID = j.JOB_ID
   WHERE j.JOB_ID = 7588
   AND m.DATE_PLACE <= '2014-08-03'
   AND m.DELETED = 'N'
   AND c.POPU > 0
GROUP BY s.SCTYPE) sub_query1 ON s.SCTYPE = sub_query1.PLACED

/* this it the query in question */
LEFT JOIN (SELECT s.SCTYPE COST, MAX(o.JTDCOST) CostJTD
   FROM jcost o
   LEFT JOIN job j ON j.JOB_ID = o.JOB_ID
   LEFT JOIN sccode s ON s.SCCODE = o.SCCODE
   LEFT JOIN ccode c ON c.SCCODE_ID = s.SCCODE_ID  
   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
/* this it the query in question */

LEFT JOIN (SELECT s.SCTYPE HOURS, SUM(l.HOURS) Hours_JTD
   FROM labor l
   INNER JOIN ccode c ON c.CCODE_ID = l.CCODE_ID
   INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   INNER JOIN job j ON j.JOB_ID = l.JOB_ID
   WHERE l.JOB_ID = 7588
   AND l.DATE_WORK <= '2014-08-03'
   AND l.DELETED = 'N'
GROUP BY s.SCTYPE) sub_query4 ON s.SCTYPE = sub_query4.HOURS
LEFT JOIN (SELECT s.SCTYPE LS, SUM(ls.LUMPSUM) LS_Updated_Variance
   FROM pcacls ls
   JOIN ccode c ON c.CCODE_ID = ls.CCODE_ID
   JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   JOIN job j ON j.JOB_ID = c.JOB_ID
   AND j.JOB_ID = 7588
   AND ls.DELETED = 'N'
   AND c.POPU > 0
GROUP BY s.SCTYPE) sub_query5 ON s.SCTYPE = sub_query5.LS
 LEFT JOIN (SELECT s.SCTYPE RATE, pr.RATE Rate_Last
   FROM pcacpr pr
   LEFT JOIN ccode c ON c.CCODE_ID = pr.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
   LEFT JOIN job j ON j.JOB_ID = c.JOB_ID
   LEFT JOIN
   (SELECT s.SCTYPE RATE, MAX(PCACPR_ID) Last_PCACPR_ID
   FROM pcacpr pr LEFT JOIN ccode c ON c.CCODE_ID = pr.CCODE_ID
   LEFT JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID AND pr.DELETED = 'N'
   AND pr.TIMESTAMP <= '2014-08-03'
   GROUP BY s.SCTYPE) Last_pr ON s.SCTYPE = Last_pr.RATE AND pr.PCACPR_ID = Last_pr.Last_PCACPR_ID
   AND j.JOB_ID = 7588
GROUP BY s.SCTYPE) sub_query6 ON s.SCTYPE = sub_query6.RATE
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;

The query below returns the proper values to be summed. However there are problems since if you replace it with the existing query (which is only different by the GROUP BY using o.SCCODE instead of s.SCTYPE), it affects other numbers in the query? But even if it is used it only shows the total of both rows not each one separately.

SELECT s.SCTYPE COST, MAX(o.JTDCOST) CostJTD
FROM jcost o
LEFT JOIN job j ON j.JOB_ID = o.JOB_ID
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;

  13683.75
  24891.28
  38862.48
107562.82
       366.20
____________
185,366.60
hdcowboyazAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gplanaCommented:
Can you please simplify your query? Don't you have an easier snipped that reproduce the issue?
0
hdcowboyazAuthor Commented:
Yes I can. I simplified it the first time and thought it solved my problem but when I put it back in the full query it broke other things. This is the simplified verison. If I change the GROUP BY s.SCTYPE to GROUP BY o.SCCODE I will get correct values. However, it will mess up other columns of data and it will only return the total of both rows not each one separately.

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 job j ON j.JOB_ID = o.JOB_ID
   LEFT JOIN sccode s ON s.SCCODE = o.SCCODE
   LEFT JOIN ccode c ON c.SCCODE_ID = s.SCCODE_ID  
   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;
0
gplanaCommented:
I think you want a sum of a column for some grouping data, and a sum of another column for some other grouping data. This is not possible to do it with just a query. You need two different queries for this, every of them with its group by.
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

hdcowboyazAuthor Commented:
I got a believe there is something you can do with in the query to get this data.
0
Ray PaseurCommented:
Please post the CREATE TABLE statements for these tables.  And please post the test data that can be loaded into these tables and that is needed to illustrate the problem.  Together, these are the SSCCE, an incredibly important concept in software development, and they are the information we need to help you get a solution.  Once we have your starting point and a clear understanding of what you want for the outputs we can almost always help!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hdcowboyazAuthor Commented:
I'll do you one better. Attached is an ERD as well as an sqldump. The JOB_ID we will use is 7398.

This query results in a column of seven rows of data.
You add the first five SCTYPE of 1 to get 63,215.75
You add the last two SCTYPE of 3 to get 21,673.43
See below. These are the correct values you should get from the main query below. The main query return only two rows of data since its grouping (SUM(CostJTD) the first five SCTYPE of 1 and the last two SCTYPE of 3

SELECT s.SCTYPE, 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 = 7398      
AND o.DELETED = 'N'
AND o.WEDATE <= '2014-08-03'
GROUP BY o.SCCODE;

14,817.43
  1,367.18
  4,147.51
41,925.46
     958.17
_________
63,215.75

20,989.65
     683.78
_________
21,673.43

Main Query but instead you get 125,857.3820,989.65

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 job j ON j.JOB_ID = o.JOB_ID
   LEFT JOIN sccode s ON s.SCCODE = o.SCCODE
   LEFT JOIN ccode c ON c.SCCODE_ID = s.SCCODE_ID  
   WHERE o.JOB_ID = 7398            
   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 = 7398
AND c.DELETED = 'N'
AND c.POPU > 0
AND s.SCTYPE NOT IN(8,9,14)
GROUP BY s.SCTYPE;

Now I know what you are going to say. What if you used GROUP BY o.SCCODE in the last line above. You get 189,728.25 and 21,628.43. Not only are they wrong but the mess up several other values in the report.

Simply put the first query returns the correct values, Summing them is another story.
ERD-JDS.jpg
jds-s-8-12-14.sql
0
hdcowboyazAuthor Commented:
This works. How do I put it in the overall query?

SELECT COST, SUM(CostJTD)
FROM (SELECT s.SCTYPE COST, MAX(o.JTDCOST) CostJTD
FROM jcost o
LEFT JOIN job j ON j.JOB_ID = o.JOB_ID
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) AS Z GROUP BY COST
0
hdcowboyazAuthor Commented:
Ray,

You asked for the below. I gave you everything and more that you asked for (which took a lot of work) and then you disappeared.

Ray Paseur2014-08-12 at 17:35:20ID: 40257315

Please post the CREATE TABLE statements for these tables.  And please post the test data that can be loaded into these tables and that is needed to illustrate the problem.  Together, these are the SSCCE, an incredibly important concept in software development, and they are the information we need to help you get a solution.  Once we have your starting point and a clear understanding of what you want for the outputs we can almost always help!
0
Ray PaseurCommented:
Sorry - I don't know why but I never got any more notifications about the question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.