Solved

SQL Syntax

Posted on 2014-01-15
10
374 Views
Last Modified: 2014-01-16
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;
0
Comment
Question by:hdcowboyaz
10 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39784740
Do you get an error if you actually put that SUM query ? At least, I don't see any obvious issue so far
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39784768
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
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 39784909
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:hdcowboyaz
ID: 39785025
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39785421
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
 

Author Comment

by:hdcowboyaz
ID: 39785435
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 39785448
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
 

Author Comment

by:hdcowboyaz
ID: 39785478
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
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 39785543
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
 

Author Comment

by:hdcowboyaz
ID: 39785576
Got it. Thanks
0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question