?
Solved

SQL Syntax

Posted on 2013-10-29
2
Medium Priority
?
276 Views
Last Modified: 2013-10-29
This query
SELECT j.JOB_ID, s.SCTYPE, SUM(c.POPU)/(SUM(c.POPU)/SUM(c.POPU/c.POUEST)) AS EstHours
FROM ccode c
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
AND (j.DELETED != 'Y'OR j.DELETED IS NULL)
AND (c.DELETED != 'Y'OR c.DELETED IS NULL)
AND (s.DELETED != 'Y'OR s.DELETED IS NULL)
GROUP BY j.JOB_ID, s.SCTYPE;

returns
JOB_ID   SCTYPE   EstHours
7398          1           3280.53
7398          3           1893.76
 
This query
SELECT a.POEST
FROM (SELECT MAX(c.POPU) POPU, c.POEST
FROM ccode c
JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
JOIN job j ON j.JOB_ID = c.JOB_ID
WHERE j.JOB_ID = 7398
AND s.SCTYPE IN (1,2,3,4,5,6,7)
AND c.POPU > 0
GROUP BY j.JOB_ID, s.SCTYPE)a;

returns
POEST
24.64
25.09

I would like to join/rewite this to ouput (Total = EstHours * POEST)
JOB_ID   SCTYPE   EstHours    POEST         TOTAL
7398          1           3280.53          24.64           80832.26
7398          3           1893.76          25.09           47514.44
ERD.jpg
0
Comment
Question by:hdcowboyaz
2 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 39609958
just include the sctype in the selected columns of your second query.
Since it's part of the group by it won't affect the results.

Then join on sctype between the two queries...

SELECT a.job_id,
       a.sctype,
       a.esthours,
       b.poest,
       (a.esthours * b.poest) AS total
  FROM (  SELECT j.job_id, s.sctype, SUM(c.popu) / (SUM(c.popu) / SUM(c.popu / c.pouest)) AS esthours
            FROM ccode c
                 INNER JOIN sccode s ON s.sccode_id = c.sccode_id
                 INNER JOIN job j ON c.job_id = j.job_id
           WHERE j.job_id = 7398
             AND s.sctype IN (1,
                              2,
                              3,
                              4,
                              5,
                              6,
                              7)
             AND c.popu > 0
             AND (j.deleted != 'Y' OR j.deleted IS NULL)
             AND (c.deleted != 'Y' OR c.deleted IS NULL)
             AND (s.deleted != 'Y' OR s.deleted IS NULL)
        GROUP BY j.job_id, s.sctype) a
       INNER JOIN (SELECT x.sctype, x.poest
                     FROM (  SELECT s.sctype, MAX(c.popu) popu, c.poest
                               FROM ccode c
                                    JOIN sccode s ON s.sccode_id = c.sccode_id
                                    JOIN job j ON j.job_id = c.job_id
                              WHERE j.job_id = 7398
                                AND s.sctype IN (1,
                                                 2,
                                                 3,
                                                 4,
                                                 5,
                                                 6,
                                                 7)
                                AND c.popu > 0
                           GROUP BY j.job_id, s.sctype) x) b
           ON a.sctype = b.sctype;
0
 

Author Closing Comment

by:hdcowboyaz
ID: 39609976
Perfect
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…
Suggested Courses

621 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