Group by to include an Avg Value within sql

Need assistance with a sql statement that contains avg(value) within the case statement.  How do I include the correct group by.

      SELECT DISTINCT  b.BEST_SUPPLIER_CD
              , B.SUPPLIER_NAME
              , D.GPA_PRD_STDT                             PREV_START_DT                                      
              , D.GPA_PRD_ENDDT                            PREV_END_DT 
              , CASE
                  WHEN CEIL(MONTHS_BETWEEN(D.GPA_PRD_ENDDT ,D.GPA_PRD_STDT ))=1 THEN 'M'
                --  WHEN CEIL(MONTHS_BETWEEN(D.GPA_PRD_ENDDT ,D.GPA_PRD_STDT ))=3 THEN 'Q'
                  ELSE ''
                END                                            KEY_TYPE 
             , AVG(D.GPA_COST)                                 PREV_MTH_COST
             , CASE WHEN AVG(D.GPA_COST) IS NULL THEN NULL
                  WHEN AVG(D.GPA_COST) < 1    THEN 'Red'
                  WHEN AVG(D.GPA_COST) >1     AND AVG(D.GPA_COST) <= 2.79 THEN 'Yellow'
                  WHEN AVG(D.GPA_COST) >2.79  AND AVG(D.GPA_COST) <= 3.79 THEN 'Bronze'
                  WHEN AVG(D.GPA_COST) >3.79 AND AVG(D.GPA_COST) <= 3.79 THEN 'Silver'
                  ELSE 'Gold' 
               END                                        PREV_MTH_COST_COLOR
            , O.DIRECTORNANME
              , O.SRMGRNAME
              , O.MGRNAME
              , C.EVALUATOR_ID
              , O.EMPNAME
              , O.ORG_STRUCTURE_CD
              , O.ORG
              
      FROM GPA_DIM                     D
        LEFT JOIN 
         GPA_EVALUATION                C 
          ON D.GPA_ID = C.GPA_ID
          AND D.GPA_PRD_STDT = C.GPA_PRD_STDT 
      LEFT JOIN 
         PERFORMANCE_BUS_GRP           B
          ON D.SUPPLIER_ID = B.SUPPLIER_ID
      LEFT JOIN ORG_DATA                                  O
          ON C.EVALUATOR_ID = O.Emp_BEMSID
      WHERE C.GPA_STAT <> 'A' 
      GROUP BY b.BEST_SUPPLIER_CD
              , B.SUPPLIER_NAME
              , C.EVALUATOR_ID 
              , O.DIRECTORNANME
              , O.SRMGRNAME
              , O.MGRNAME
              , C.EVALUATOR_ID
              , O.EMPNAME
              , O.ORG_STRUCTURE_CD
              , O.ORG

Open in new window


What am I missing.  Just need the correct syntax to include the avg value.

Thanks,

Karen
Karen SchaeferBI ANALYSTAsked:
Who is Participating?
 
awking00Commented:
I've taken some liberties in modifying your original query, most notably the removal of the distinct keyword and use of the case statement evaluation order.
SELECT BEST_SUPPLIER_CD, SUPPLIER_NAME, PREV_START_DT, PREV_END_DT, KEYTYPE, PREV_MTH_COST
,CASE WHEN PREV_MTH_COST IS NULL THEN NULL
        WHEN PREV_MTH_COST <= 1    THEN 'Red'
        WHEN PREV_MTH_COST <= 2.79 THEN 'Yellow'
        WHEN PREV_MTH_COST <= 3.79 THEN 'Bronze'
        WHEN PREV_MTH_COST > 3.79 THEN 'Silver'
        ELSE 'Gold' 
 END    PREV_MTH_COST_COLOR
,DIRECTORNANME
,SRMGRNAME
,MGRNAME
,EVALUATOR_ID
,EMPNAME
,ORG_STRUCTURE_CD
,ORG
FROM
(SELECT b.BEST_SUPPLIER_CD
  , B.SUPPLIER_NAME
  , D.GPA_PRD_STDT     PREV_START_DT  
  , D.GPA_PRD_ENDDT    PREV_END_DT 
  , CASE WHEN CEIL(MONTHS_BETWEEN(D.GPA_PRD_ENDDT ,D.GPA_PRD_STDT ))=1 THEN 'M'
    --   WHEN CEIL(MONTHS_BETWEEN(D.GPA_PRD_ENDDT ,D.GPA_PRD_STDT ))=3 THEN 'Q'
         ELSE ''
    END  KEY_TYPE 
 , AVG(D.GPA_COST)   PREV_MTH_COST
, O.DIRECTORNANME
  , O.SRMGRNAME
  , O.MGRNAME
  , C.EVALUATOR_ID
  , O.EMPNAME
  , O.ORG_STRUCTURE_CD
  , O.ORG
FROM GPA_DIM   D
  LEFT JOIN 
   GPA_EVALUATION    C 
    ON D.GPA_ID = C.GPA_ID
    AND D.GPA_PRD_STDT = C.GPA_PRD_STDT 
LEFT JOIN 
   PERFORMANCE_BUS_GRP     B
    ON D.SUPPLIER_ID = B.SUPPLIER_ID
LEFT JOIN ORG_DATA    O
    ON C.EVALUATOR_ID = O.Emp_BEMSID
WHERE C.GPA_STAT <> 'A' 
GROUP BY b.BEST_SUPPLIER_CD
  , B.SUPPLIER_NAME
  , C.EVALUATOR_ID 
  , O.DIRECTORNANME
  , O.SRMGRNAME
  , O.MGRNAME
  , C.EVALUATOR_ID
  , O.EMPNAME
  , O.ORG_STRUCTURE_CD
  , O.ORG
)

Open in new window

1
 
Mark GeerlingsDatabase AdministratorCommented:
I always question Oracle queries that start like this:
"SELECT DISTINCT ..."
In my experience, that is usually not the best way to get the job done in Oracle.

You have a "group by" clause at the end of your query already.  That is usually better than "select distinct..." at the top.

You are trying to do both:
AVG(D.GPA_COST)  PREV_MTH_COST
plus:
CASE WHEN AVG(D.GPA_COST) ...
at the same level in a query?  I don't believe that is supported.

If you use a nested query (an "in-line view") or a "with" sub-query to get most of your values including the AVG(D.GPA_COST) with a "group by" clause, but without the
CASE WHEN AVG(D.GPA_COST) ...
then you could get the CASE... simply in the outer query with no problem.
0
 
SujithData ArchitectCommented:
All your projections(columns in the select list) without the aggregate functions in should be there in the group by, something like this -

and yes, you dont need the distinct

      SELECT    b.BEST_SUPPLIER_CD
              , B.SUPPLIER_NAME
              , D.GPA_PRD_STDT                             PREV_START_DT                                      
              , D.GPA_PRD_ENDDT                            PREV_END_DT 
              , CASE
                  WHEN CEIL(MONTHS_BETWEEN(D.GPA_PRD_ENDDT ,D.GPA_PRD_STDT ))=1 THEN 'M'
                  ELSE ''
                END                                            KEY_TYPE 
             , AVG(D.GPA_COST)                                 PREV_MTH_COST
             , CASE WHEN AVG(D.GPA_COST) IS NULL THEN NULL
                  WHEN AVG(D.GPA_COST) < 1    THEN 'Red'
                  WHEN AVG(D.GPA_COST) >1     AND AVG(D.GPA_COST) <= 2.79 THEN 'Yellow'
                  WHEN AVG(D.GPA_COST) >2.79  AND AVG(D.GPA_COST) <= 3.79 THEN 'Bronze'
                  WHEN AVG(D.GPA_COST) >3.79 AND AVG(D.GPA_COST) <= 3.79 THEN 'Silver'
                  ELSE 'Gold' 
               END                                        PREV_MTH_COST_COLOR
            , O.DIRECTORNANME
              , O.SRMGRNAME
              , O.MGRNAME
              , C.EVALUATOR_ID
              , O.EMPNAME
              , O.ORG_STRUCTURE_CD
              , O.ORG
      FROM GPA_DIM                     D
        LEFT JOIN 
         GPA_EVALUATION                C 
          ON D.GPA_ID = C.GPA_ID
          AND D.GPA_PRD_STDT = C.GPA_PRD_STDT 
      LEFT JOIN 
         PERFORMANCE_BUS_GRP           B
          ON D.SUPPLIER_ID = B.SUPPLIER_ID
      LEFT JOIN ORG_DATA                                  O
          ON C.EVALUATOR_ID = O.Emp_BEMSID
      WHERE C.GPA_STAT <> 'A' 
      GROUP BY 
                b.BEST_SUPPLIER_CD
              , B.SUPPLIER_NAME
              , D.GPA_PRD_STDT                                                                 
              , D.GPA_PRD_ENDDT                            
              , CASE
                  WHEN CEIL(MONTHS_BETWEEN(D.GPA_PRD_ENDDT ,D.GPA_PRD_STDT ))=1 THEN 'M'
                  ELSE ''
                END                                            
              , O.DIRECTORNANME
              , O.SRMGRNAME
              , O.MGRNAME
              , C.EVALUATOR_ID
              , O.EMPNAME
              , O.ORG_STRUCTURE_CD
              , O.ORG

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Geert GOracle dbaCommented:
my moto:
a query starting with "select distinct" is a "select that stinks"

our other moto:
ITSS : It's The Stupid Sql
or when the query writer always writes "select distinct": It's The Sql, Stupid

in 90% of the cases, the query writer starting every query with "select distinct" is because someone once said it's good practice
which it isn't, it's just a bad habit

someone who does that, usually hasn't the faintest clue what the distinct is doing
it sorts all data and removes duplicates
people forget that sorting is a costly effort
0
 
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks guys for the great assist.

Karen
0
 
SujithData ArchitectCommented:
Thank You, glad to help
0
 
awking00Commented:
You're most welcome.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.