troubleshooting Question

Query Syntax - Max, Sum

Avatar of hdcowboyaz
hdcowboyazFlag for United States of America asked on
MySQL Server
9 Comments1 Solution186 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros