SQL Syntax, Select

This query works and outputs one row of data.

SELECT
       st.SCTYPE 'Desc', st.UM 'UoM', FORMAT(SUM(c.POPU), 0) 'Orig Qty', ROUND(SUM(c.POPU) / SUM(c.POPU / c.POUEST), 0) 'Orig PR', 
       c.POEST 'Orig HR', FORMAT(SUM(c.POPU / c.POUEST), 0) 'Total Hours', 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', m.Placed_JTD
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 st ON s.SCTYPE = st.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 st ON s.SCTYPE = st.SCTYPE_ID
            INNER JOIN job j ON m.JOB_ID = j.JOB_ID
                  WHERE (st.SCTYPE = 'rebar' OR st.SCTYPE = 'caissons')
                  AND m.JOB_ID = 7398
                  AND m.DATE_PLACE <= '2013-10-6'
                  AND c.TM = 'N'
                  AND (m.DELETED != 'Y'OR m.DELETED IS NULL)
                 AND st.DELETED = 'N'
           ) m
WHERE  (st.SCTYPE_ID = 1 OR st.SCTYPE_ID = 7)
AND j.JOB_ID = 7398
GROUP BY st.SCTYPE; 

Open in new window


This query works and displays one row and one field only, but I want to add this column SUM(l.HOURS) AS Hours_JTD to the above query. This will add one table labor l to the above query.

SELECT SUM(l.HOURS) AS Hours_JTD
FROM labor l, ccode c, sccode s, sctype st
WHERE (c.CCODE_ID = l.CCODE_ID)
AND (s.SCCODE_ID = c.SCCODE_ID)
AND (st.SCTYPE_ID = s.SCTYPE)
AND (st.SCTYPE = 'rebar' OR st.SCTYPE = 'caissons')
AND l.JOB_ID = 7398
AND l.DATE_WORK <= '2013-10-6'
AND c.TM = 'N'
AND (l.DELETED != 'Y' OR l.DELETED IS NULL)
GROUP BY l.JOB_ID;

Open in new window


I am attaching the ERD as well as the currrent output of the first query.
ERD.jpg
Output.jpg
hdcowboyazAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hnasrCommented:
Compare with:

SELECT q1.f1, q1f2, q2.f1
FROM q1 INNER JOIN q2 ON q1.f = q2.f;

replace q1 and q2 with your queries.

SELECT q1.f1, q1f2, q2.f1
FROM (SELECT ..... ) AS q1 INNER JOIN (SELECT ... ) AS q2
ON q1.f = q2.f;
0
hdcowboyazAuthor Commented:
Thats nice theory but it doesn't solve the query.
0
hnasrCommented:
Ok. Can you add the sql to recreate the tables with some test data, so I can check?

My comment was a guide to apply, but you did not comment on the result of this application. This is what I intend to do when you send the script to recreate the issue.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

hdcowboyazAuthor Commented:
I'm putting together he tables and data. Should have by tonight.
0
hdcowboyazAuthor Commented:
Current Query for new data attached as sqdump

SELECT
       st.SCTYPE 'Desc', st.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', 
       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', 
       m.Placed_JTD 'Placed_JTD',
       m.Placed_JTD 'Hours_JTD',
       m.Placed_JTD 'Cost_JTD',
       m.Placed_JTD 'Actual PR',
       m.Placed_JTD 'Unit Cost JTD',
       m.Placed_JTD 'Est Cost',
       m.Placed_JTD 'Variance',
       m.Placed_JTD '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 st ON s.SCTYPE = st.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 st ON s.SCTYPE = st.SCTYPE_ID
            INNER JOIN job j ON m.JOB_ID = j.JOB_ID
                  WHERE m.JOB_ID = 7001
                  AND m.DATE_PLACE <= '2013-10-6'
                  AND c.TM = 'N'
                  AND (m.DELETED != 'Y'OR m.DELETED IS NULL)
                  AND st.DELETED = 'N'
           ) m
WHERE  (st.SCTYPE_ID = 1 OR st.SCTYPE_ID = 7)
AND j.JOB_ID = 7001
GROUP BY st.SCTYPE;

Open in new window

steel-10-19-13.sql
0
hdcowboyazAuthor Commented:
0
hnasrCommented:
Can't see Hours in labor table.

I suggest you recreate the issue simplifying the two queries using up to 3 tables.

Doing that you may solve the problem yourself.
0
hdcowboyazAuthor Commented:
I've attached a new ERD which shows l.HOURS.  There is to be only one query.  You have the tables with data and an ERD. All 7 tables are requried, This is a simple query. There are more columns remaining once this is resloved.
ERD.jpg
0
hnasrCommented:
"This is a simple query"
For me it is not. I expect 3 tables including labor, with 3 to 4 fields.
List few records for each input table, and list expected output.

Once you find the idea, you may expand required tables and fields as you wish.
0
RobOwner (Aidellio)Commented:
Have you considered not trying to do everything in the one query?
From experience I've always found it easier to break it up into much smaller queries and create views before bringing it all together. That way you can test and know the data is correct at each stage. It may not be as efficient but at least you know it works and where you're going wrong. Also possible to migrate it all to one query later if you wanted to. You may also find these separate queries/views can be re used in other queries.
I'll try to find some time to set up an example for you with your structure.
0
hdcowboyazAuthor Commented:
It's already done by separate queries this is the point I'm trying to make one query. Can no one out there do this?
0
RobOwner (Aidellio)Commented:
Ok go easy, I've just come across this thread. It was your cross join sub query that led me to believe you could simplify this query further by removing that as well. As I said, give me some time to review the structure and ERD
0
SharathData EngineerCommented:
Can you confirm if this is correct?
 
SCTYPE_ID SCTYPE 
1		  rebar
7		  caissons

Open in new window

Also why do you want to display same Placed_JTD and Hours_JTD for two different SCTYPE?
Do you want to display the aggregate data of Placed_JTD and Hours_JTD for each SCTYPE?
0
Julian HansenCommented:
Late commer but on query 1 you are grouping on st.SCTYPE and in the labour query you are grouping on l.hours - seems to be a conflict.

Have you considered defining the second query as a view (but without the filter on JOBID) and joining to that on to the first query by jobID?
0
hnasrCommented:
Info to help needed: From input and expected output, one can think of process. If process is known, one can think of output and ways to reorganize input in proper form.
http:#a39587360
0
PortletPaulfreelancerCommented:
Your existing larger query already contains a technique to combine 2 queries into one - by using "cross apply". I think that was the result of a similar question.

Alternatively, the same logic that was used in another prior question can be applied here, a correlated subquery.

You have 2 working queries, both are returning just one row, and one of these just one column - so that second small query can be embedded into the selection clause of the larger query (or embedded as a cross apply).

Same caveat as last time - a correlated subquery in the selection list isn't necessarily the most efficient way - but with such small result sets it is not going to be noticeable here.

Given this same need has been asked several times it will probably be worthwhile if you try it first, if you get into difficulty with it then let us know.

-----------------
If you wanted a more sophisticated solution than these approaches we would need all table definitions, sample data for each table - as well as the expected results (while we can read sql quite easily it is quite another predicting the effect of joins without being able to asses a query with data).
0
hdcowboyazAuthor Commented:
Sharath_123
Can you confirm if this is correct?
SCTYPE_ID                  SCTYPE
1              rebar
7              caissons

- Yes, this is correct

Also why do you want to display same Placed_JTD and Hours_JTD for two different SCTYPE?
 - We are combining the results of 1 & 7 together.

Do you want to display the aggregate data of Placed_JTD and Hours_JTD for each SCTYPE
 - Yes, but I was trying to make the query simple so I used the first SCTYPE (1 & 7) in the query. I was going to use PHP to run through the rest of them.

julianH
On query 1 you are grouping on st.SCTYPE and in the labour query you are grouping on l.hours - seems to be a conflict.
- Yes the querries are grouped differently. They return completely different values;
 
Have you considered defining the second query as a view (but without the filter on JOBID) and joining to that on to the first query by jobID?
- I don't know views enough to answer that but I have thought they may be the way to go.

hsnar
Info to help needed: From input and expected output, one can think of process. If process is known, one can think of output and ways to reorganize input in proper form.
- I'm not looking for generic statements

PortletPaul
- Yes I agree. I was going to try what you did on the last one. I will see if it works and put up the results.
0
Julian HansenCommented:
- Yes the querries are grouped differently. They return completely different values;
Then how do you expect to combine them.

A view is just a named query.

So

CREATE VIEW vwLabourHours AS
SELECT SUM(l.HOURS) AS Hours_JTD
FROM labor l, ccode c, sccode s, sctype st
WHERE (c.CCODE_ID = l.CCODE_ID)
AND (s.SCCODE_ID = c.SCCODE_ID)
AND (st.SCTYPE_ID = s.SCTYPE)
AND (st.SCTYPE = 'rebar' OR st.SCTYPE = 'caissons')
AND c.TM = 'N'
AND (l.DELETED != 'Y' OR l.DELETED IS NULL)
GROUP BY l.JOB_ID;

You can then achieve the same results by
SELECT * FROM vwLabourHours WHERE l.JOB_ID = 7398 AND l.DATE_WORK <= '2013-10-6';

You can also then join the view into the first query and link it on the JOB_ID.

Of course you could simply join the second query to the first as a subquery - but the view approach might make it easier - it could get quite convoluted the other way.
0
Ray PaseurCommented:
I wonder what the economic benefit might be in trying to do this all in one query.  I'm in the same camp with @tagit and @julianH.  It's probably easier to deconstruct the problem into bite-sized pieces, rather than trying to eat the elephant all at once.
0
hdcowboyazAuthor Commented:
This was the solution..............

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(o.Cost_JTD, 0))  'Cost_JTD',
       FORMAT(m.Placed_JTD/l.Hours_JTD, 2) 'Actual PR',
       FORMAT(o.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) - o.Cost_JTD, 0)) 'Variance',
       FORMAT(o.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 SUM(Cost_JTD) Cost_JTD
    FROM (
        SELECT MAX(o.JTDCOST) Cost_JTD
        FROM jcost o
        INNER JOIN sccode s ON s.SCCODE = o.SCCODE
        INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
        WHERE o.JOB_ID = 7398
        AND t.SCTYPE_ID IN (1,7)
        AND o.DELETED = 'N'
        GROUP BY o.SCCODE
    ) A
) o
WHERE j.JOB_ID = 7398
AND t.SCTYPE_ID IN (1,7)
GROUP BY t.SCTYPE;
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
hdcowboyazAuthor Commented:
See above
0
hdcowboyazAuthor Commented:
Leave me alone
0
hdcowboyazAuthor Commented:
I just did
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.