asked on

SQL Syntax

What I really want is where c.POPU is MAX

SELECT
s.SCTYPE,
ROUND(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU), 4) 'UNIT COST'
FROM ccode c
INNER JOIN job j ON c.JOB_ID = j.JOB_ID
INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE j.JOB_ID = 7398
AND c.POPU > 1
GROUP BY t.SCTYPE;

Guy Hengel [angelIII / a3]

you could add a "simple" condition like this:

SELECT
     s.SCTYPE,
     ROUND(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU), 4) 'UNIT COST'
     FROM ccode c
     INNER JOIN job j ON c.JOB_ID = j.JOB_ID
     INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
     INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE j.JOB_ID = 7398
AND c.POPU > 1
and c.popu = ( select max ( x.popu ) from ccode x )
GROUP BY t.SCTYPE;

Open in new window

this new subquery might need more, especially if you want this for the relevant job

SELECT
     s.SCTYPE,
     ROUND(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU), 4) 'UNIT COST'
     FROM ccode c
     INNER JOIN job j ON c.JOB_ID = j.JOB_ID
     INNER JOIN sccode s ON s.SCCODE_ID = c.SCCODE_ID
     INNER JOIN sctype t ON t.SCTYPE_ID = s.SCTYPE
WHERE j.JOB_ID = 7398
AND c.POPU > 1
and c.popu = ( select max ( x.popu ) from ccode x where x.job_id = j.job_id )
GROUP BY t.SCTYPE;

Open in new window

hope this helps

hdcowboyaz

ASKER

Query : SELECT s.SCTYPE, ROUND(SUM(c.POPU / c.POUEST) * c.POEST / SUM(c.POPU), 4) 'UNIT COST' FROM ccode c INNER JOI...
Error Code : 1305
FUNCTION jds.max does not exist

Guy Hengel [angelIII / a3]

that error code is not really related to the code posted?

hdcowboyaz

ASKER

Yes it is. Why does it make sense for me to post some other error?

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

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

Start Free Trial