Derived Column Nested Select SUM

Transact-SQL
1

So I am trying to run the following Derived Column Query:

SELECT USD_AMT, atr3, INV_NUM, INV_DATE, ERR_MSG, CSTMR_NM, TYPE, PROCESSED_FLAG
FROM
(
SELECT sum(ABC) AS USD_AMT, atr3, INV_NUM, INV_DATE, ERR_MSG, CSTMR_NM, TYPE, PROCESSED_FLAG
FROM DB
) d
WHERE INV_DATE>='01-OCT-2022'
AND USD_AMT >= '10000' AND
TYPE IN ('A','B','C')
AND PROCESSED_FLAG = 'E'
GROUP BY
USD_AMT, atr3, INV_NUM, INV_DATE, ERR_MSG, CSTMR_NM, TYPE, PROCESSED_FLAG
ORDER BY
USD_AMT desc

And get the following error message: [Error] Execution (28: 111): ORA-00979: not a GROUP BY expression

What am I doing wrong? I am a bit new to SQL.

Thank you!

2

hi

hope this helps

SELECT 
    Sum(abc) AS USD_AMT
       , atr3
       , inv_num
       , inv_date
       , err_msg
       , cstmr_nm
       , type
       , processed_flag
FROM   
   db
WHERE  
    inv_date >= '01-OCT-2022'
        AND 
	usd_amt >= '10000'
       AND 
    type IN ( 'A', 'B', 'C' )
       AND 
    processed_flag = 'E'
GROUP  BY 
          atr3
        , inv_num
        , inv_date
        , err_msg
        , cstmr_nm
        , type
        , processed_flag
ORDER  BY 
      Sum(abc) DESC
3

Hi,

No unfortunately this does not work as the error message is [Error] Execution (39: 2): ORA-00904: "USD_AMT": invalid identifier. I tried this initialy but it does not work. It works with Derived Column rather than Calculated Column but somehow something is missing but thank you!

4

This is a MS SQL Server site - and you are using Oracle. Any help we could provide may not work in Oracle.

You stated you have a derived column - but there is no derived column in your query. What I do see is an attempt to create a derived table but that is not correctly formed:

This is the derived table - which has SUM and would require a GROUP BY.

You then have in your GROUP BY - this SUM column as the first column to group by, which cannot be resolved because it should be the result of the grouping.

In SQL Server - we can reference the column name of 'derived columns' in the order by. If you cannot do that in Oracle then you would need to do what @harishgg1 provided - which is restating the SUM.

What @harishgg1 provided should work the same in either SQL Server or Oracle. The error you are getting wouldn't be generated by the query that was provided.

5

Let me know if this one works for you:

SELECT sum(ABC) AS USD_AMT, atr3, INV_NUM, INV_DATE, ERR_MSG, CSTMR_NM, TYPE, PROCESSED_FLAG
FROM DB
WHERE INV_DATE>='01-OCT-2022'
AND TYPE IN ('A','B','C')
AND PROCESSED_FLAG = 'E'
GROUP BY ABC, atr3, INV_NUM, INV_DATE, ERR_MSG, CSTMR_NM, TYPE, PROCESSED_FLAG
HAVING SUM(ABC) >= 10000
ORDER BY SUM(ABC) desc