Link to home
Start Free TrialLog in
Avatar of hdcowboyaz
hdcowboyazFlag for United States of America

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.Hours_JTD, 2) 'Actual PR',
       FORMAT(jc.Cost_JTD/m.Placed_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;
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

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
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
Avatar of hdcowboyaz

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
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?

Every derived table must have its own alias
Correct 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
...

Open in new window

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'
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 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
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Got it. Thanks