hdcowboyaz
asked on
SQL Syntax
This query needs incorporated in the query below:
SELECT SUM(Cost_JTD)
FROM
(
SELECT MAX(jc.JTDCOST) Cost_JTD
FROM jcost jc
INNER JOIN sccode s ON s.SCCODE = jc.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE jc.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND jc.DELETED = 'N'
GROUP BY jc.SCCODE
)
A;
It needs to replace the similar cross query at the bottom in BOLD. I want to return the SUM, not several rows of MAX(Cost_JTD)
SELECT
t.SCTYPE 'Desc', t.UM 'UoM',
FORMAT(SUM(c.POPU), 0) 'Orig Qty',
ROUND(SUM(c.POPU) / SUM(c.POPU / c.POUEST), 0) 'Orig PR',
FORMAT(c.POEST, 2) 'Orig HR',
FORMAT(SUM(c.POPU / c.POUEST), 0) 'Total Hours',
CONCAT('$', FORMAT(SUM(c.POPU / c.POUEST) * c.POEST, 0)) 'Est Cost',
ROUND(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU), 4) 'Est Unit Cost',
FORMAT(m.Placed_JTD, 0) 'Placed_JTD',
FORMAT(l.Hours_JTD, 0) 'Hours_JTD',
CONCAT('$', FORMAT(jc.Cost_JTD, 0)) 'Cost_JTD',
FORMAT(m.Placed_JTD/l.Hour s_JTD, 2) 'Actual PR',
FORMAT(jc.Cost_JTD/m.Place d_JTD, 4) 'Unit Cost JTD',
CONCAT('$', FORMAT(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU) * m.Placed_JTD, 0)) 'Est Cost',
CONCAT('$', FORMAT((SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU) * m.Placed_JTD) - jc.Cost_JTD, 0)) 'Variance',
FORMAT(jc.Cost_JTD/l.Hours _JTD, 2) 'Avg HR'
FROM ccode c
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode s ON c.SCCODE_ID = s.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
CROSS JOIN (
SELECT SUM(m.PLACEMENT) Placed_JTD
FROM materials m
INNER JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
INNER JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
INNER JOIN job j ON m.JOB_ID = j.JOB_ID
WHERE m.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND m.DATE_PLACE <= '2014-01-15'
AND c.TM = 'N'
AND m.DELETED = 'N'
) m
CROSS JOIN (
SELECT 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 sctype t ON s.SCTYPE = t.SCTYPE_ID
INNER JOIN job j ON j.JOB_ID = l.JOB_ID
WHERE l.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND l.DATE_WORK <= '2014-01-15'
AND c.TM = 'N'
AND l.DELETED = 'N'
) l
CROSS JOIN (
SELECT MAX(jc.JTDCOST) Cost_JTD
FROM jcost jc
INNER JOIN sccode s ON s.SCCODE = jc.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE jc.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND jc.DELETED = 'N'
) jc
WHERE j.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
GROUP BY t.SCTYPE;
SELECT SUM(Cost_JTD)
FROM
(
SELECT MAX(jc.JTDCOST) Cost_JTD
FROM jcost jc
INNER JOIN sccode s ON s.SCCODE = jc.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE jc.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND jc.DELETED = 'N'
GROUP BY jc.SCCODE
)
A;
It needs to replace the similar cross query at the bottom in BOLD. I want to return the SUM, not several rows of MAX(Cost_JTD)
SELECT
t.SCTYPE 'Desc', t.UM 'UoM',
FORMAT(SUM(c.POPU), 0) 'Orig Qty',
ROUND(SUM(c.POPU) / SUM(c.POPU / c.POUEST), 0) 'Orig PR',
FORMAT(c.POEST, 2) 'Orig HR',
FORMAT(SUM(c.POPU / c.POUEST), 0) 'Total Hours',
CONCAT('$', FORMAT(SUM(c.POPU / c.POUEST) * c.POEST, 0)) 'Est Cost',
ROUND(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU), 4) 'Est Unit Cost',
FORMAT(m.Placed_JTD, 0) 'Placed_JTD',
FORMAT(l.Hours_JTD, 0) 'Hours_JTD',
CONCAT('$', FORMAT(jc.Cost_JTD, 0)) 'Cost_JTD',
FORMAT(m.Placed_JTD/l.Hour
FORMAT(jc.Cost_JTD/m.Place
CONCAT('$', FORMAT(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU) * m.Placed_JTD, 0)) 'Est Cost',
CONCAT('$', FORMAT((SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU) * m.Placed_JTD) - jc.Cost_JTD, 0)) 'Variance',
FORMAT(jc.Cost_JTD/l.Hours
FROM ccode c
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode s ON c.SCCODE_ID = s.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
CROSS JOIN (
SELECT SUM(m.PLACEMENT) Placed_JTD
FROM materials m
INNER JOIN `release` r ON m.RELEASE_ID = r.RELEASE_ID
INNER JOIN ccode c ON r.CCODE_ID = c.CCODE_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON s.SCTYPE = t.SCTYPE_ID
INNER JOIN job j ON m.JOB_ID = j.JOB_ID
WHERE m.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND m.DATE_PLACE <= '2014-01-15'
AND c.TM = 'N'
AND m.DELETED = 'N'
) m
CROSS JOIN (
SELECT 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 sctype t ON s.SCTYPE = t.SCTYPE_ID
INNER JOIN job j ON j.JOB_ID = l.JOB_ID
WHERE l.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND l.DATE_WORK <= '2014-01-15'
AND c.TM = 'N'
AND l.DELETED = 'N'
) l
CROSS JOIN (
SELECT MAX(jc.JTDCOST) Cost_JTD
FROM jcost jc
INNER JOIN sccode s ON s.SCCODE = jc.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE jc.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND jc.DELETED = 'N'
) jc
WHERE j.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
GROUP BY t.SCTYPE;
Do you get an error if you actually put that SUM query ? At least, I don't see any obvious issue so far
yes , you can try this
SELECT Sum(jc.JTDCOST) Cost_JTD
FROM jcost jc
INNER JOIN sccode s ON s.SCCODE = jc.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE jc.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND jc.DELETED = 'N'
and let us know what eror you are getting
SELECT Sum(jc.JTDCOST) Cost_JTD
FROM jcost jc
INNER JOIN sccode s ON s.SCCODE = jc.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE jc.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND jc.DELETED = 'N'
and let us know what eror you are getting
SUBQUERY the MAX and SUM all of the MAX's
CROSS JOIN (
SELECT SUM(Cost_JTD_T) Cost_JTD FROM (
SELECT MAX(jct.JTDCOST) Cost_JTD_T
FROM jcost jct
INNER JOIN sccode s ON s.SCCODE = jct.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE jct.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND jc.DELETED = 'N'
)
) jc
CROSS JOIN (
SELECT SUM(Cost_JTD_T) Cost_JTD FROM (
SELECT MAX(jct.JTDCOST) Cost_JTD_T
FROM jcost jct
INNER JOIN sccode s ON s.SCCODE = jct.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE jct.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND jc.DELETED = 'N'
)
) jc
ASKER
by: Guy Hengel
Yes I get an error is if i put in the SUM querry. That is why I posted. I get the errors below. The query works alone but not when I just paste it in. I'm missing some syntax.
Query : SELECT t.SCTYPE 'Desc', t.UM 'UoM', FORMAT(SUM(c.POPU), 0) 'Orig Qty', ROUND(SUM(c.POPU) / SUM(c.POPU / c...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT SUM(Cost_JTD)
FROM
(
SELECT MAX(jc.JTDCOST) Cost_JTD
FROM jcost jc
INNER ' at line 48
Query : WHERE j.JOB_ID = 7398 AND t.SCTYPE_ID IN (1,7) GROUP BY t.SCTYPE
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE j.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
GROUP BY t.SCTYPE' at line 1
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
by: Pratima Pharande
Query : SELECT t.SCTYPE 'Desc', t.UM 'UoM', FORMAT(SUM(c.POPU), 0) 'Orig Qty', ROUND(SUM(c.POPU) / SUM(c.POPU / c...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT Sum(jc.JTDCOST) Cost_JTD
FROM jcost jc
INNER JOI' at line 48
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- -
by: NerdsOfTech
Query : SELECT t.SCTYPE 'Desc', t.UM 'UoM', FORMAT(SUM(c.POPU), 0) 'Orig Qty', ROUND(SUM(c.POPU) / SUM(c.POPU / c...
Error Code : 1248
Every derived table must have its own alias
Yes I get an error is if i put in the SUM querry. That is why I posted. I get the errors below. The query works alone but not when I just paste it in. I'm missing some syntax.
Query : SELECT t.SCTYPE 'Desc', t.UM 'UoM', FORMAT(SUM(c.POPU), 0) 'Orig Qty', ROUND(SUM(c.POPU) / SUM(c.POPU / c...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT SUM(Cost_JTD)
FROM
(
SELECT MAX(jc.JTDCOST) Cost_JTD
FROM jcost jc
INNER ' at line 48
Query : WHERE j.JOB_ID = 7398 AND t.SCTYPE_ID IN (1,7) GROUP BY t.SCTYPE
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE j.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
GROUP BY t.SCTYPE' at line 1
--------------------------
by: Pratima Pharande
Query : SELECT t.SCTYPE 'Desc', t.UM 'UoM', FORMAT(SUM(c.POPU), 0) 'Orig Qty', ROUND(SUM(c.POPU) / SUM(c.POPU / c...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT Sum(jc.JTDCOST) Cost_JTD
FROM jcost jc
INNER JOI' at line 48
--------------------------
by: NerdsOfTech
Query : SELECT t.SCTYPE 'Desc', t.UM 'UoM', FORMAT(SUM(c.POPU), 0) 'Orig Qty', ROUND(SUM(c.POPU) / SUM(c.POPU / c...
Error Code : 1248
Every derived table must have its own alias
Many of the filters are the same, so is there a reason you are using CROSS JOIN versus JOINing the tables by the JOB_ID and performing the aggregates at that level?
i.e.,
Every derived table must have its own aliasCorrect but also note the sample query did not have the GROUP BY; therefore, it did not match your query at top of question. Try copying it in with the alias and see what happens.
i.e.,
...
CROSS JOIN (
SELECT SUM(Cost_JTD)
FROM (
SELECT MAX(jc.JTDCOST) Cost_JTD
FROM jcost jc
INNER JOIN sccode s ON s.SCCODE = jc.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE jc.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND jc.DELETED = 'N'
GROUP BY jc.SCCODE
) A
) jc
...
ASKER
Query : SELECT t.SCTYPE 'Desc', t.UM 'UoM', FORMAT(SUM(c.POPU), 0) 'Orig Qty', ROUND(SUM(c.POPU) / SUM(c.POPU / c...
Error Code : 1054
Unknown column 'jc.Cost_JTD' in 'field list'
Error Code : 1054
Unknown column 'jc.Cost_JTD' in 'field list'
I copied your code, and I didn't catch that you did not give the SUM an alias. Just add Cost_JTD to SELECT.
i.e., SELECT SUM(Cost_JTD) Cost_JTD ...
P.S. I see the outer query groups on t.SCTYPE; therefore, check that your SUMs do not need to be by SCTYPE. Again, I think you can GROUP BY JOB_ID[, SCTYPE] in the inner queries and then JOIN on those (if you use both) in the outer query.
i.e., SELECT SUM(Cost_JTD) Cost_JTD ...
P.S. I see the outer query groups on t.SCTYPE; therefore, check that your SUMs do not need to be by SCTYPE. Again, I think you can GROUP BY JOB_ID[, SCTYPE] in the inner queries and then JOIN on those (if you use both) in the outer query.
ASKER
I used this...
CROSS JOIN (
SELECT SUM(Cost_JTD) Cost_JTD
FROM (
SELECT MAX(jc.JTDCOST) Cost JTD
FROM jcost jc
INNER JOIN sccode s ON s.SCCODE = jc.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE jc.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND jc.DELETED = 'N'
GROUP BY jc.SCCODE
) A
) jc
Query : SELECT t.SCTYPE 'Desc', t.UM 'UoM', FORMAT(SUM(c.POPU), 0) 'Orig Qty', ROUND(SUM(c.POPU) / SUM(c.POPU / c...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JTD
FROM jcost jc
INNER JOIN sccode s ON s.SCCODE = jc.SCCODE
' at line 51
CROSS JOIN (
SELECT SUM(Cost_JTD) Cost_JTD
FROM (
SELECT MAX(jc.JTDCOST) Cost JTD
FROM jcost jc
INNER JOIN sccode s ON s.SCCODE = jc.SCCODE
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE jc.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
AND jc.DELETED = 'N'
GROUP BY jc.SCCODE
) A
) jc
Query : SELECT t.SCTYPE 'Desc', t.UM 'UoM', FORMAT(SUM(c.POPU), 0) 'Orig Qty', ROUND(SUM(c.POPU) / SUM(c.POPU / c...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'JTD
FROM jcost jc
INNER JOIN sccode s ON s.SCCODE = jc.SCCODE
' at line 51
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it. Thanks