CODEWITHSUNDEEP
oracle-databaseplsqlgroup-by
esandrkwn
esandrkwn

Reputation: 399

GROUP BY the CASE statement output

How can I change the script below so I can do GROUP BY YR, QTR, CODE the value QTR_PCT gives to get my desired output?

CREATE VIEW REPORTS.MY_VIEW
(YR, QTR, QTR_PCT, CODE)
AS 
SELECT YR, QTR
, CASE WHEN PCTPERF>=85 THEN ROUND(AVG(PCTPERF)) 
  ELSE NULL
  END QTR_PCT
, CODE
FROM ANOTHER_VIEW
GROUP BY YR, QTR, PCTPERF, CODE
ORDER BY CODE ASC;

Actual Output: (I didn't include column QTR_PCT)

+------+-----+------+
|   YR | QTR | CODE |
+------+-----+------+
| 2012 |   1 | 1039 | 
| 2012 |   1 | 1039 | 
| 2012 |   1 | 1039 |
| 2012 |   2 | 1039 | 
| 2012 |   2 | 1039 | 
| 2012 |   2 | 1039 |  
+------+-----+------+

Desired output: (I didn't include column QTR_PCT)

+------+-----+------+
|   YR | QTR | CODE |
+------+-----+------+
| 2012 |   1 | 1039 | 
| 2012 |   2 | 1039 | 
+------+-----+------+

Upvotes: 0

Views: 261

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16915

I'm not sure that I understand your question but maybe this can help, try:

SELECT YR, qtr, code,max(qtr_pct)  aa
FROM (
SELECT YR, qtr, code, pctref,  CASE WHEN pctref >= 85 THEN round(avg(pctref) over (partition BY YEAR, qtr, code))
  ELSE NULL END qtr_pct
FROM ANOTHER_VIEW) t
GROUP BY YR, qtr, code;

Here is a sqlfiddle demo

Upvotes: 1

Related Questions