Solved

SQL Syntax

Posted on 2014-01-15
10
368 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
 

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

744 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now