Raju Raju - 4 months ago 31
SQL Question

SUM on a column from subquery in ORACLE

Im trying to take Total transaction count from a table`between the given date on oracle database.I wrote a subquery to group unique transactions and when i try to sum across the columns in subquery i get an error ORA-00904: ColumnName : invalid identifier. Im new to oracle but this same used to work for me in sql server.

Here is my query.

select sum(Tots),sum(CRIR),sum(RT),sum(Succes) from(

select ds.LOAN_ID,ds.CUST_ID,TO_CHAR(ds.SENT_DT_TIME, 'YYYY-MM-DD') "Dates", count(*) "Tots",
SUM(DECODE (ds.STATUS, 'CR', 1,'IR',1,0)) "CRIR",
SUM(DECODE (ds.STATUS, 'R', 1,'T',1,0)) "RT",
SUM(DECODE (ds.STATUS, 'S', 1, 0)) "Succes"
WHERE TRUNC(ds.SENT_DT_TIME) BETWEEN to_date('2016-10-04','yyyy-mm-dd') and to_date('2016-10-07','yyyy-mm-dd')


Your query would be much more simply written as:

select count(*),
       sum(case when status in ('CR', 'IR') then 1 else 0 end) as CRIR, 
       sum(case when status in ('R', 'T') then 1 else 0 end) as RT, 
       sum(case when status in ('S') then 1 else 0 end) as Succes
from data_string ds
where ds.sent_dt_time >= date '2016-10-04' and
      ds.sent_dt_time < date '2016-10-08';


  • Two levels of aggregation is not needed for what you want to do.
  • Use case instead of decode(), because case is standard SQL. It is also simpler to incorporate logic such as in.
  • Oracle supports the date keyword, which can be followed by a standard date.
  • Almost exactly the same query works in SQL Server (minus the date keyword).