Solved

SQL Syntax

Posted on 2014-01-15
10
390 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 143

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
Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

 

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 60

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 60

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 60

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

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

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

Creating and Managing Databases with phpMyAdmin in cPanel.
This article shows the steps required to install WordPress on Azure. Web Apps, Mobile Apps, API Apps, or Functions, in Azure all these run in an App Service plan. WordPress is no exception and requires an App Service Plan and Database to install
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

726 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