CODEWITHSUNDEEP
sqloracle-databasegroup-by
RageQwit
RageQwit

Reputation: 125

not a GROUP BY expression error in Oracle

I'm new to Oracle and I'm wondering if anyone can tell me what is wrong with my GROUP BY expression. I've tried removing several of the columns in the GROUP BY and nothing works still. There is another query this one shares a UNION with but I've removed that to cut down on reading.

 -- Selection Criteria: Date Range = 12/01/2011 to 12/31/2011; AO Transaction Fee = 4.95%

  SELECT 
    SUBSTR(Transaction_Date,1,10) Transaction_Date
, Item_Number
, REGEXP_REPLACE(SUBSTR(Item_Or_Adj_Description,1,50)
,'([^[:print:]])',' ') AS Item_Or_Adj_Desctription
, Customer_Type
, Document_ID
, Dealer_ID
, Sales_Type
, Item_Quantity
, Total_Fee
, State_Fee
, Transaction_Fee
, AO_Fee
, WDFW_Fee 

  FROM 
  ( 
    -- Sales Transactions 
   SELECT /*+ index(IT ITEM_X4) */ 
      TO_CHAR(IT.it_status_set_date - 2/24, 'MM/DD/YYYY') AS Transaction_Date,  -- Pacific Time
      TO_NUMBER(IT.ic_rcn) AS Item_Number, IT.it_descr AS Item_Or_Adj_Description, 
      DT.di_name AS Customer_Type, IT.it_docid AS Document_ID, IT.ag_id AS Dealer_ID, 
      CASE WHEN UPPER(IST.is_name) = 'ACTIVE' THEN 'SALE' ELSE IST.is_name END AS Sales_Type, 
      NVL(IT.it_quantity * CASE WHEN IT.is_id = 'AC' THEN 1 WHEN IT.is_id = 'DU' THEN 1 ELSE -1 END, 0) AS Item_Quantity,  -- Dups = 1
      NVL(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) + 
    NVL(IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS Total_Fee, 
      NVL(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS State_Fee, 
      NVL(IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END, 0.00) AS Transaction_Fee, 
      CASE WHEN IT.it_other_fee IS NULL OR IT.it_other_fee = 0.00 THEN 0.00 
        ELSE ROUND(IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END * (4.95 / 100), 2) 
      END AS AO_Fee, 
      CASE WHEN IT.it_other_fee IS NULL OR IT.it_other_fee = 0.00 THEN 0.00 
        ELSE (IT.it_other_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END) - 
          ROUND((IT.it_state_fee * CASE WHEN IT.is_id IN ('DC','SC') THEN -1.00 ELSE 1.00 END * (4.95 / 100)), 2) 
      END AS WDFW_Fee 
   FROM ITEM IT 
   JOIN DISCOUNT_TYPE DT ON DT.di_id = IT.di_id 
   JOIN ITEM_STATUS_TYPE IST ON IST.is_id = IT.is_id 
   WHERE IT.it_status_ind = 'A' -- Include active ITEM rows only. 
   AND (IT.is_id IN ('AC','DC','SC') OR (IT.is_id = 'DU' AND NVL(IT.it_state_fee, 0) != 0)) -- Exclude voids, exchanges, and false duplicates.
   AND IT.ic_rcn != '999' -- Exclude Dealer Fees. 
   AND IT.it_status_set_date BETWEEN TO_DATE('12/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS') + 2/24  -- Pacific Time
                 AND TO_DATE('12/31/2011 23:59:59', 'MM/DD/YYYY HH24:MI:SS') + 2/24 )


  GROUP BY TO_DATE(SUBSTR(Transaction_Date,1,10), 'MM/DD/YYYY')
           , Item_Number
           , Item_Or_Adj_Description
           ,Customer_Type
           ,Document_ID
           ,Dealer_ID
           ,Sales_Type
           ,Item_Quantity;
          -- ,Total_Fee
         --  ,State_Fee
          -- ,Transaction_Fee
        --  ,AO_Fee
        --  ,WDFW_Fee

Upvotes: 1

Views: 3025

Answers (2)

Justin Cave
Justin Cave

Reputation: 231851

It does not appear that you are doing any aggregation in your query (none of the columns in your SELECT list is an aggregate function like COUNT or MAX). Given that, you should just remove the GROUP BY clause entirely.

If, as phlogratos suggest, you are trying to use a GROUP BY to eliminate duplicates, you're doing something wrong. Most commonly, the problem is that the query itself shouldn't be generating duplicate rows but some join condition or predicate was missed causing rows to get duplicated. If that is the case, you're much better off fixing the underlying problem and adding in the missing predicate or the missing join. If you really expect the inline view to return duplicate rows and you want to remove them, you're much better off doing a SELECT DISTINCT <<list of columns>> with no GROUP BY clause.

Upvotes: 2

phlogratos
phlogratos

Reputation: 13924

You have to remove the columns in the select list, not in the group by clause, or you might add additional columns in the group by clause. All columns in the select list must be mentioned in the group by clause or have to be aggregate functions like min, max or count.

Upvotes: 2

Related Questions