asked on

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..

PortletPaul

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.

hdcowboyaz

ASKER

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'

PortletPaul

change

sc.

st.

in line 19

LEFT JOIN (
SELECT
m.JOB_ID
, c.SCCODE_ID
>>> , st.SCTYPE <<<<<<<

hdcowboyaz

ASKER

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

PortletPaul

it should be a SINGLE value?
(746,006)

i.e. it should be that value on every row?

hdcowboyaz

ASKER

Yes it's a single value, since it's a SUM. SUM(m.PLACEMENT)

hdcowboyaz

ASKER

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

PortletPaul

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

hdcowboyaz

ASKER

Sorry. Yes there should only be one row. It worked but there are two rows. See attached.

hdcowboyaz

ASKER

Output of query. As you can see the first row is not needed.
output.jpg

PortletPaul

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)

PortletPaul

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

hdcowboyaz

ASKER

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.

PortletPaul

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

hdcowboyaz

ASKER

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

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

hdcowboyaz

ASKER

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;

hdcowboyaz

ASKER

Thanks

PortletPaul

no problem... it's often awkward to describe what one is aiming at (in both directions)
glad I could help.

Cheers, Paul