Getting an oracle error not a GROUP BY expression

Question

I am getting a not a group by expression when creating a view the error i get is for this line "param.ports_per_olt8 * SUM (card.in_svc_ind) AS total_port_cnt, " Any help?

My params are like this below

WITH param
    AS (SELECT eid,
                 17-SUM(DECODE(NVL(EQUIPPED_TYPE, ASSIGNED_TYPE),'ETOS10',1,0)) AS gpon_slots_per_ta500X,
               8 AS ports_per_olt8,
               4 AS etos10_slots_per_ta5000, 
               10 AS ports_per_etos10,
                8 as ports_per_TA1108VP  
      FROM ECIL_TA500X_CARD
      GROUP BY EID) 


            (  SELECT   card.eid,
                    COUNT ( * ) AS installed_card_cnt, 
                    SUM (card.in_svc_ind) AS insrv_card_cnt, 
                    param.ports_per_olt8 * SUM (card.in_svc_ind) 
                    AS total_port_cnt,  --ERROR HERE--
                    (param.ports_per_olt8 * SUM (card.in_svc_ind))
                    - SUM (DECODE (card.in_svc_ind, 1, card.oos_port_cnt, 0))
                       AS insrv_port_cnt
             FROM   ecil_ta500x_card card, param  
            WHERE   card.equipped_type like 'OLT8%'
            AND     card.eid = param.eid 
           GROUP BY   card.eid) oltcard,

ECIL_TA500X_CARD is a table and params are created in this view in the beginning

SoulTrain · Accepted Answer · 2014-03-25T22:11:13.190

The param.ports_per_olt8 column is not a part of any aggregation, that's why you are getting this error. If it has a distinct value for every row, which I think it has - try using MIN(param.ports_per_olt8) where ever the column is used.

WITH param
    AS (SELECT eid,
                 17-SUM(DECODE(NVL(EQUIPPED_TYPE, ASSIGNED_TYPE),'ETOS10',1,0)) AS gpon_slots_per_ta500X,
               8 AS ports_per_olt8,
               4 AS etos10_slots_per_ta5000, 
               10 AS ports_per_etos10,
                8 as ports_per_TA1108VP  
      FROM ECIL_TA500X_CARD
      GROUP BY EID) 


            (  SELECT   card.eid,
                    COUNT ( * ) AS installed_card_cnt, 
                    SUM (card.in_svc_ind) AS insrv_card_cnt, 
                    MIN(param.ports_per_olt8) * SUM (card.in_svc_ind) 
                    AS total_port_cnt,  --ERROR HERE--
                    (MIN(param.ports_per_olt8) * SUM (card.in_svc_ind))
                    - SUM (DECODE (card.in_svc_ind, 1, card.oos_port_cnt, 0))
                       AS insrv_port_cnt
             FROM   ecil_ta500x_card card, param  
            WHERE   card.equipped_type like 'OLT8%'
            AND     card.eid = param.eid 
           GROUP BY   card.eid) oltcard,

if i add SUM(param.ports_per_olt8) instead of the MIN will it give me the same result? I added SUM to it and i did not get no errors. so should i keep it SUM or change it to MIN — Gio, Mar 26 '14 at 17:52
Actually that's not the same, you wont get an error but your result will be different.. — SoulTrain, Mar 26 '14 at 18:22

Getting an oracle error not a GROUP BY expression

1 Answers1