SQL Syntax

This query is correct.

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'
FROM ccode c, job j, sccode sc, sctype st
WHERE c.SCCODE_ID = sc.SCCODE_ID
AND c.JOB_ID = j.JOB_ID
AND sc.SCTYPE = st.SCTYPE_ID
AND (st.SCTYPE_ID = 1 OR st.SCTYPE_ID = 7)
AND j.JOB_ID = 7398
GROUP BY st.SCTYPE_ID;

and this query is correct

SELECT SUM(m.PLACEMENT) UNITS_PLACED
FROM materials m, `release` r, ccode c, sccode s, sctype st, job j
WHERE (j.JOB_ID = m.JOB_ID)
AND r.RELEASE_ID = m.RELEASE_ID
AND c.CCODE_ID = r.CCODE_ID
AND c.SCCODE_ID = s.SCCODE_ID
AND s.SCTYPE = st.SCTYPE_ID
AND (c.SCCODE_ID = 4 OR c.SCCODE_ID = 116 OR c.SCCODE_ID = 131)
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';

I want to add the column on the second query to the first query. When I try to merge them I get a Cartesion Product and first query numbers become wrong..
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.

PortletPaulfreelancerCommented:
Those 2 queries are quite different, particularly the tables and where conditions. For example the 2nd query limits results to withinn 3 ccode.sccode_id (4,116,131) and this may affect the overall results - so I have used a left (outer) join between the 2 queries - with the 2nd query treated as a nested subquery.
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'
     , sum(m.UNITS_PLACED) as UNITS_PLACED
FROM ccode c
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode sc ON c.SCCODE_ID = sc.SCCODE_ID
INNER JOIN sctype st ON sc.SCTYPE = st.SCTYPE_ID
LEFT JOIN (
            SELECT
                   m.JOB_ID
                 , c.SCCODE_ID
                 , sc.SCTYPE
                 , SUM(m.PLACEMENT) UNITS_PLACED
            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 (
                         c.SCCODE_ID = 4
                      OR c.SCCODE_ID = 116
                      OR c.SCCODE_ID = 131
                   )
                 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'
            GROUP BY
                   m.JOB_ID
                 , c.SCCODE_ID
                 , sc.SCTYPE
            ) m ON c.JOB_ID = m.JOB_ID
               AND c.SCCODE_ID = m.SCCODE_ID
               AND sc.SCTYPE = m.SCTYPE
WHERE  (
             st.SCTYPE_ID = 1
          OR st.SCTYPE_ID = 7
       )
AND j.JOB_ID = 7398
GROUP BY
       st.SCTYPE
     , st.UM
     , c.POEST
;

Open in new window

{+ 3 edits - v.sorry  - typos} I have also added 2 fields in the final group by, I know MySQL permits laxity here - but this is what is actually happening.
0
hdcowboyazAuthor Commented:
I got this error...

Query : SELECT        st.SCTYPE 'Desc'      , st.UM 'UoM'      , FORMAT(SUM(c.POPU), 0) 'Orig Qty'      , ROUND(SUM(c.POPU) / SUM(c.POPU...
Error Code : 1054
Unknown column 'sc.SCTYPE' in 'field list'

But there is a column 'sc.SCTYPE'
0
PortletPaulfreelancerCommented:
change
sc.
to
st.
in line 19

LEFT JOIN (
            SELECT
                   m.JOB_ID
                 , c.SCCODE_ID
   >>>        , st.SCTYPE       <<<<<<<
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

hdcowboyazAuthor Commented:
Got it. Also changed line 43

            GROUP BY
                   m.JOB_ID
                 , c.SCCODE_ID
                 , sc.SCTYPE    (st.SCTYPE)

It ran this time and all numbers were correct except the last column we moved in Units Placed.

It was Null, should be 746,006
0
PortletPaulfreelancerCommented:
it should be a SINGLE value?
 (746,006)

i.e. it should be that value on every row?
0
hdcowboyazAuthor Commented:
Yes it's a single value, since it's a SUM.    SUM(m.PLACEMENT)
0
hdcowboyazAuthor Commented:
This is the lastest on the query. Nothing has changed but I made some slight changes. THe first 8 fields are correct but the last column SUM(m.PLACEMENT) 'PlacedJTD' has a null for an output.

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.PlacedJTD
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
LEFT JOIN (
            SELECT
                   m.JOB_ID
                 , c.SCCODE_ID
                 , st.SCTYPE
                 , SUM(m.PLACEMENT) 'PlacedJTD'
            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'
            GROUP BY
                   m.JOB_ID
                 , c.SCCODE_ID
                 , st.SCTYPE
            ) m ON c.JOB_ID = m.JOB_ID
               AND c.SCCODE_ID = m.SCCODE_ID
               AND s.SCTYPE = m.SCTYPE
WHERE  (
             st.SCTYPE_ID = 1
          OR st.SCTYPE_ID = 7
       )
AND j.JOB_ID = 7398
GROUP BY st.SCTYPE_ID
;

Open in new window

0
PortletPaulfreelancerCommented:
yep, SUM(something) will produce a single number

my question was really, are you expecting the same number on every row?

note I have no data or results to 'see', so I am utterly dependent on you being my eyes

perhaps try this, which I expect will repeat 746,006 for every row
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.UNITS_PLACED
FROM ccode c
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode sc ON c.SCCODE_ID = sc.SCCODE_ID
INNER JOIN sctype st ON sc.SCTYPE = st.SCTYPE_ID
CROSS JOIN (
            SELECT
                   SUM(m.PLACEMENT) UNITS_PLACED
            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 (
                         c.SCCODE_ID = 4
                      OR c.SCCODE_ID = 116
                      OR c.SCCODE_ID = 131
                   )
                 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
     , st.UM
     , c.POEST
     , m.UNITS_PLACED
;

Open in new window

0
hdcowboyazAuthor Commented:
Sorry. Yes there should only be one row. It worked but there are two rows. See attached.
0
hdcowboyazAuthor Commented:
Output of query. As you can see the first row is not needed.
output.jpg
0
PortletPaulfreelancerCommented:
attached IS NULL :)

>>Yes there should only be one row
so why are you grouping the results at all?

e.g.
SELECT
       st.SCTYPE 'Desc'

...
WHERE  (
             st.SCTYPE_ID = 1
          OR st.SCTYPE_ID = 7
       )
GROUP BY
       st.SCTYPE
This MUST produce 2 rows because there are 2 st.SCTYPE's (1 and 7)
0
PortletPaulfreelancerCommented:
attachment IS NOT NULL now :) thanks

try this:
SELECT
       MAX(st.SCTYPE) 'Desc'
     , MAX(st.UM) 'UoM'
     , FORMAT(SUM(c.POPU), 0) 'Orig Qty'
     , ROUND(SUM(c.POPU) / SUM(c.POPU / c.POUEST), 0) 'Orig PR'
     , MAX(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.UNITS_PLACED
FROM ccode c
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode sc ON c.SCCODE_ID = sc.SCCODE_ID
INNER JOIN sctype st ON sc.SCTYPE = st.SCTYPE_ID
CROSS JOIN (
            SELECT
                   SUM(m.PLACEMENT) UNITS_PLACED
            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 (
                         c.SCCODE_ID = 4
                      OR c.SCCODE_ID = 116
                      OR c.SCCODE_ID = 131
                   )
                 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
;

Open in new window

0
hdcowboyazAuthor Commented:
Error

Query : SELECT        MAX(st.SCTYPE) 'Desc'      , MAX(st.UM) 'UoM'      , FORMAT(SUM(c.POPU), 0) 'Orig Qty'      , ROUND(SUM(c.POPU) / ...
Error Code : 1140
Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

----------------------------------------------------------------
There are thousands of rows of data. Filtering SCTYPE 1 or 7 limits the results to only those few hundred rows of data that we then sum up.
0
PortletPaulfreelancerCommented:
That wasn't the point of my comment
- yes, I understand you may need to FILTER (in the where clause)
BUT
if you then GROUP BY [field1], [field2],[field3]
then you will get one row for each unique combination of  [field1], [field2],[field3]
(i.e. more than one row)
---------------------------------------------
>>"This query is correct."
so, I have assumed this to be true (so far)
but it isn't true because it produces more than one row

So, what do you want to do with the fields which cause the unwanted row(s)?
I don't know this - only you do.

Below I have highlighted the items you need to make decisions on:
SELECT
       st.SCTYPE 'Desc'                  --<< what do you want to do with this? MAX()?
     , st.UM 'UoM'                       --<< what do you want to do with this? MAX()?
     , FORMAT(SUM(c.POPU), 0) 'Orig Qty'
     , ROUND(SUM(c.POPU) 
           / SUM(c.POPU 
           / c.POUEST)                   --<< what do you want to do with this? SUM()?
           , 0) 'Orig PR'
     , c.POEST 'Orig HR'
     , FORMAT(SUM(c.POPU 
                / c.POUEST               --<< what do you want to do with this? SUM()?
                ), 0) 'Total Hours'
     , FORMAT(SUM(c.POPU /
                  c.POUEST)              --<< what do you want to do with this? SUM()?
                  * c.POEST              --<< what do you want to do with this? SUM()?
                  , 0) 'Est Cost'
     , ROUND(SUM(c.POPU 
                / c.POUEST               --<< what do you want to do with this? SUM()?
                ) * c.POEST              --<< what do you want to do with this? SUM()?
                / SUM(c.POPU)
                , 4) 'Est Unit Cost'
     , m.PlacedJTD                        --<< this will be handled by MAX() as it is only one value

Open in new window

0
hdcowboyazAuthor Commented:
Im not undeerstanding what you are asking the query before worked it just had the first row which should not be there.

SELECT
       st.SCTYPE 'Desc'                  --<< what DO you want TO DO WITH this? MAX()? NO, this IS TEXT
     , st.UM 'UoM'                       --<< what DO you want TO DO WITH this? MAX()? NO, this IS TEXT
     , FORMAT(SUM(c.POPU), 0) 'Orig Qty'
     , ROUND(SUM(c.POPU)
           / SUM(c.POPU
           / c.POUEST)                   --<< what DO you want TO DO WITH this? SUM()? SUM
           , 0) 'Orig PR'
     , c.POEST 'Orig HR'
     , FORMAT(SUM(c.POPU
                / c.POUEST               --<< what DO you want TO DO WITH this? SUM()? SUM
                ), 0) 'Total Hours'
     , FORMAT(SUM(c.POPU /
                  c.POUEST)              
                  * c.POEST              --<< what DO you want TO DO WITH this? SUM()? SUM
                  , 0) 'Est Cost'
     , ROUND(SUM(c.POPU
                / c.POUEST              
                ) * c.POEST              --<< what DO you want TO DO WITH this? SUM()? SUM
                / SUM(c.POPU)
                , 4) 'Est Unit Cost'
     , m.PlacedJTD                        --<< this will be handled BY MAX() AS it IS only ONE VALUE SUM
0
PortletPaulfreelancerCommented:
>> it just had the first row which should not be there.
'just' a fact you omitted to mention in the question
i.e. it is not 100% correct

To remove that unwanted row requires that you decide what to do with c.POUEST

Does it get summed? or do we use a MAX()?
(I don't know the answer)

Then, you also have to decide what to to do with:  st.SCTYPE 'Desc'  & , st.UM 'UoM'
(I would suggest MAX() )
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:
Ok. I understand.. it worked. I only wnat to group by st.SCTYPE;

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;
0
hdcowboyazAuthor Commented:
Thanks
0
PortletPaulfreelancerCommented:
no problem... it's often awkward to describe what one is aiming at (in both directions)
glad I could help.

Cheers, Paul
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.