CODEWITHSUNDEEP
sqloracle-databasesubquery
Raju
Raju

Reputation: 207

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"
  FROM DATA_STRING ds
  WHERE TRUNC(ds.SENT_DT_TIME) BETWEEN to_date('2016-10-04','yyyy-mm-dd') and to_date('2016-10-07','yyyy-mm-dd')
  Group by ds.LOAN_ID,ds.CUST_ID,TO_CHAR(ds.SENT_DT_TIME, 'YYYY-MM-DD')
);

Upvotes: 1

Views: 2666

Answers (2)

AlexSmet
AlexSmet

Reputation: 2161

Just for your error ORA-00904: ColumnName : invalid identifier

If you use quoted aliases for columns "Tots", "CRIR", "RT", "Succes" in subquery, then you need use the same quoted aliases in main query.

Quoted aliases are case sensitive. But nonquoted aliases are not case sensitive. Oracle interprets them as uppercase.

It will be work:

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"
  FROM DATA_STRING ds
  WHERE TRUNC(ds.SENT_DT_TIME) BETWEEN to_date('2016-10-04','yyyy-mm-dd') and to_date('2016-10-07','yyyy-mm-dd')
  Group by ds.LOAN_ID,ds.CUST_ID,TO_CHAR(ds.SENT_DT_TIME, 'YYYY-MM-DD')
);

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269703

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';

Notes:

  • 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).

Upvotes: 1

Related Questions