CODEWITHSUNDEEP
sqloracle-databasegroup-by
user4876141
user4876141

Reputation:

Results of SQL grouping not as intended

Intro Details:

I have an issue with a query for Oracle SQL Developer using client 12c. I've researched several other questions on SO as well as searched Google for an answer to this, but ultimately the answers to all have been to include all columns without aggregate functions in the GROUP BY clause.

What I want is to get a single result for each category (PG.CAT) per year, week, and day (FD.YEAR, FD.WEEK, and FD.DT respectively). I want to sum the units, hours, errors (GE.QTY), and total hours. I also perform multiplication and division on two columns and join 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

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 that I have above, why would this not be giving me the intended result? Thank you all in advance for any guidance provided.

Upvotes: 3

Views: 63

Answers (1)

Stefan Yordanov
Stefan Yordanov

Reputation: 666

Try to SUM/AVG (depending on what you need) 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;

Upvotes: 2

Related Questions