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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

awking00Information Technology SpecialistCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Karen SchaeferBI ANALYSTAuthor Commented:
Thanks guys for the great assist.

Karen
0
SujithData ArchitectCommented:
Thank You, glad to help
0
awking00Information Technology SpecialistCommented:
You're most welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PL/SQL

From novice to tech pro — start learning today.