CODEWITHSUNDEEP
sqloracle-database
Vishal Tyagi
Vishal Tyagi

Reputation: 580

How i can i subtract two selected columns in the same query?

select (select count(*) from tbl_account) as processed,
(select count(*) from tbl_rejected_account) as rejected,
processed -rejected as approved from tbl_control .

how can i get that 'approved' count without having to write two subqueries and subtract them later.

EDIT:

The original Query that i want to change:-

 select 
ACTIVITY_DATE 
,SYSTEM_NAME 
,START_TIME 
,END_TIME 
,INSTANCE_NO as instance_number
,case status when '1' then 'Success'
when '2' then 'In process'
when '3' then 'Failed' end as status

,(select count(distinct account_number) from tbl_account_detail where   coretype=a.system_name and INSTANCE_NO=a.instance_no and a.activity_date=to_char(upload_date,'dd-MON-yy'))+

(select count(distinct account_number) from tbl_account_detail_exception where  system_name=a.system_name and INSTANCE_NO=a.instance_no and a.activity_date=to_char(upload_date,'dd-MON-yy'))
as AccountCount
,(select count(distinct account_number) from tbl_account_detail where  CREATOR='SYSTEM' and APPROVER='SYSTEM' and system_name=a.system_name and INSTANCE_NO=a.instance_no and a.activity_date=to_char(upload_date,'dd-MON-yy')) 
as AutoApprovedCount
    ,(select count(distinct account_number) from tbl_account_detail where  coretype=a.system_name and INSTANCE_NO=a.instance_no and a.activity_date=to_char(upload_date,'dd-MON-yy')) +

 (select count(distinct account_number) from tbl_account_detail_exception where  system_name=a.system_name and INSTANCE_NO=a.instance_no and 

a.activity_date=to_char(upload_date,'dd-MON-yy')) -

(select count(distinct account_number) from tbl_account_detail where a.activity_date=to_char(upload_date,'dd-MON-yy') and CREATOR='SYSTEM' and APPROVER='SYSTEM' and system_name=a.system_name and INSTANCE_NO=a.instance_no)
    as MaintenanceCount
    from tbl_control_file_status a where activity_type='MAIN' and activity_name='START';

clearly this is not what should be the proper way,kindly provide an alternative solution.

Upvotes: 2

Views: 7300

Answers (2)

Andomar
Andomar

Reputation: 238086

You can use a subquery to introduce aliases for use in the outer query:

select  SubQueryAlias.*
,       processed - rejected as approved
from    (
        select  (
                select count(*) 
                from   tbl_account
                ) as processed,            
                (
                select  count(*) 
                from    tbl_rejected_account
                ) as rejected
        from    dual
        ) as SubQueryAlias
;

It's often more readable to use a common-table expression (CTE) as in Alex Poole's answer.

Upvotes: 2

Alex Poole
Alex Poole

Reputation: 191275

You can't refer to a column alias in the same level of query it is defined, except in an order by clause. That is because of the way the query is processed and the result set constructed, but also avoids ambiguity. This is mentioned in the documentatin:

Specify an alias for the column expression. Oracle Database will use this alias in the column heading of the result set. The AS keyword is optional. The alias effectively renames the select list item for the duration of the query. The alias can be used in the order_by_clause but not other clauses in the query.

You could use a CTE for each subquery, or inline views:

select ta.processed, tra.rejected, ta.processed - tra.rejected as approved
from (
  select count(*) as processed from tbl_account
) ta
cross join (
  select count(*) as rejected from tbl_rejected_account
) tra

Or if you really have a correlation with the third table:

select tc.id, ta.processed, tra.rejected, ta.processed - tra.rejected as approved
from tbl_control tc
join (
  select id, count(*) as processed from tbl_account group by id
) ta on ta.id = tc.id
join (
  select id, count(*) as rejected from tbl_rejected_account group by id
) tra on tra.id = tc.id

You haven't said what the relationship is so I've assumed a common ID column. Using CTEs rather than inline views that would look like:

with ta as (
  select id, count(*) as processed from tbl_account group by id
), tra as (
  select id, count(*) as rejected from tbl_rejected_account group by id
)
select tc.id, ta.processed, tra.rejected, ta.processed - tra.rejected as approved
from tbl_control tc
join ta on ta.id = tc.id
join tra on tra.id = tc.id

You may need outer joins and nvl if either subquery table might not have matching rows.

You don't really need to use subqueries, inline views or CTEs here though; you can just join the tables and have the aggregates in the top-level query - you need to duplicate the count not the whole subquery:

with ta as (
  select id, count(*) as processed from tbl_account group by id
), tra as (
  select id, count(*) as rejected from tbl_rejected_account group by id
)
select tc.id, count(ta.id) as processed,
  count(tra.id) as rejected,
  count(ta.id) - count(tra.id) as approved
from tbl_control tc
join tbl_approved ta on ta.id = tc.id
join tbl_rejected tra on tra.id = tc.id
group by tc.id

You can add more joins and conditions as needed of course.

Upvotes: 1

Related Questions