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;
hdcowboyazAsked:
Who is Participating?
 
Kevin CrossChief Technology OfficerCommented:
SELECT MAX(jc.JTDCOST) Cost JTD

Notice you have an error here with a space instead of "_".
I think you just need to double check aliases and parenthesis in the entire code block.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Do you get an error if you actually put that SUM query ? At least, I don't see any obvious issue so far
0
 
Pratima PharandeCommented:
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
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
NerdsOfTechTechnology ScientistCommented:
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
0
 
hdcowboyazAuthor Commented:
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
0
 
Kevin CrossChief Technology OfficerCommented:
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

0
 
hdcowboyazAuthor Commented:
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'
0
 
Kevin CrossChief Technology OfficerCommented:
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.
0
 
hdcowboyazAuthor Commented:
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
0
 
hdcowboyazAuthor Commented:
Got it. Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.