SQL grouping results are not intended
Membership information:
I have a query issue for Oracle SQL Developer using 12c client. I researched several other questions on SO and also searched Google for an answer to this question, but ultimately the answers to all were to include all non-aggregate columns in the sentence GROUP BY
.
I want to get a single result for each category ( PG.CAT
) in year, week and day ( FD.YEAR
, FD.WEEK
and FD.DT
accordingly). I want to sum units, hours, errors ( GE.QTY
) and total hours. I also do multiplication and division on two columns and concatenating up to four other tables.
Query:
`SELECT
FD.YEAR,
FD.WEEK,
PG.DT,
PG.CAT,
SUM(PG.UNITS) AS UNITS,
SUM(PG.HOURS) AS HOURS,
PM.MTM,
PM.MTM * PG.HOURS AS ADJ_MTM,
(PG.UNITS / (PM.MTM * PG.HOURS)) AS PERC_STANDARD,
SUM(CASE WHEN GE.QTY IS NULL THEN 0 ELSE GE.QTY END) AS QTY,
SUM(WH.TOTALHOURS) AS TOTALHOURS
FROM
PROD_GUNS PG
INNER JOIN PROD_MTM PM
ON PG.CAT = PM.CATEGORY
AND PM.DEPTNO = '018'
AND PG.DT BETWEEN PM.START_DT AND PM.END_DT
INNER JOIN FISCAL_DATES_DAYS FD
ON PG.DT = FD.DT
LEFT OUTER JOIN PROD_GUNS_ERRORS GE
ON PG.EID = GE.EID
AND PG.DT = GE.DT
INNER JOIN WEEKLY_HOURS WH
ON FD.WEEK = WH.DT_WEEK
AND FD.YEAR = WH.DT_YEAR
AND PG.EID = WH.EEXX31
GROUP BY
FD.YEAR,
FD.WEEK,
PG.DT,
PG.CAT,
PM.MTM,
PM.MTM * PG.HOURS,
(PG.UNITS / ( PM.MTM * PG.HOURS))
HAVING
FD.YEAR = '2015'
AND FD.WEEK = '1'
AND PG.DT = '29-DEC-14'
AND PG.CAT = 'Picking'
ORDER BY
PG.DT;`
Actual result:
2015 1 29-DEC-14 Picking 46 0.5 68 34 1.35294117647058823529411764705882352941 0 32.21
2015 1 29-DEC-14 Picking 831 7.72 68 524.96 1.58297775068576653459311185614142029869 0 29.35
The intended result:
2015 1 20-Dec-14 Picking 877 8.22 68 558.96 1.21654501216545 0 61.59
Question:
With the aggregates and grouping I have above, why doesn't this give me the intended output? Thank you for all the recommendations provided.
Try SUM / AVG (whichever you want) PM.MTM * PG.HOURS AS ADJ_MTM and (PG.UNITS / (PM.MTM * PG.HOURS)) AS PERC_STANDARD, not group by them:
SELECT
FD.YEAR,
FD.WEEK,
PG.DT,
PG.CAT,
SUM(PG.UNITS) AS UNITS,
SUM(PG.HOURS) AS HOURS,
PM.MTM,
SUM(PM.MTM * PG.HOURS )AS ADJ_MTM,
SUM((PG.UNITS / (PM.MTM * PG.HOURS))) AS PERC_STANDARD,
SUM(CASE WHEN GE.QTY IS NULL THEN 0 ELSE GE.QTY END) AS QTY,
SUM(WH.TOTALHOURS) AS TOTALHOURS
FROM
PROD_GUNS PG
INNER JOIN PROD_MTM PM
ON PG.CAT = PM.CATEGORY
AND PM.DEPTNO = '018'
AND PG.DT BETWEEN PM.START_DT AND PM.END_DT
INNER JOIN FISCAL_DATES_DAYS FD
ON PG.DT = FD.DT
LEFT OUTER JOIN PROD_GUNS_ERRORS GE
ON PG.EID = GE.EID
AND PG.DT = GE.DT
INNER JOIN WEEKLY_HOURS WH
ON FD.WEEK = WH.DT_WEEK
AND FD.YEAR = WH.DT_YEAR
AND PG.EID = WH.EEXX31
GROUP BY
FD.YEAR,
FD.WEEK,
PG.DT,
PG.CAT,
PM.MTM
HAVING
FD.YEAR = '2015'
AND FD.WEEK = '1'
AND PG.DT = '29-DEC-14'
AND PG.CAT = 'Picking'
ORDER BY
PG.DT;
source to share