We help IT Professionals succeed at work.

Query Syntax - Max, Sum

hdcowboyaz
hdcowboyaz asked
on
183 Views
Last Modified: 2014-11-17
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Can you please simplify your query? Don't you have an easier snipped that reproduce the issue?

Author

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;
CERTIFIED EXPERT

Commented:
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.

Author

Commented:
I got a believe there is something you can do with in the query to get this data.
Most Valuable Expert 2011
Author of the Year 2014
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

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

Author

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

Author

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!
Most Valuable Expert 2011
Author of the Year 2014

Commented:
Sorry - I don't know why but I never got any more notifications about the question.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.