hdcowboyaz
asked on
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_I D, 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_JT D, 2)) 'Actual PR',
/*14*/FORMAT(CostJTD/Place d_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
#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_I
/*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_JT
/*14*/FORMAT(CostJTD/Place
/*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)/
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
Can you please simplify your query? Don't you have an easier snipped that reproduce the issue?
ASKER
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;
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;
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.
ASKER
I got a believe there is something you can do with in the query to get this data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.38 & 20,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
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.38 & 20,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
ASKER
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
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
ASKER
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!
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!
Sorry - I don't know why but I never got any more notifications about the question.