CODEWITHSUNDEEP
sqloracle-database
Maciaz
Maciaz

Reputation: 1224

How do I replace multiple, same subqueries?

I do have such query, where multiple sum subqueries are called. With clause doesn't seem to work here.

select 
    a.id, 
    a.balance as main_balance, 
    (select sum(amount) from sub_account sa where sa.account_id = a.id) as sub_account_sum, 
    a.balance - (select sum(amount) from sub_account sa where sa.account_id = a.id) as diff
from accounts a 
inner join sub_account sa on sa.account_id = a.id
where a.balance != (select sum(amount) from sub_account sa where sa.account_id = a.id)
and a.balance - (select sum(amount) from sub_account sa where sa.account_id = a.id) < 5000
and a.balance - (select sum(amount) from sub_account sa where sa.account_id = a.id) > -5000
and (select count(*) from sub_account sa where sa.account_id = a.id) > 1

The subquery (select sum(amount) from sub_account sa where sa.account_id = a.id) is called 5 times. Basically I would like to replace it in every place with something better looking.

Upvotes: 0

Views: 509

Answers (4)

hakobot
hakobot

Reputation: 186

    With sum_addi as
    (select sa.account_id , sum(amount) as summ from sub_account sa 
    having count(*) > 1 
and  sum(amount) < 5000 
and  sum(amount) > -5000)
    select 
        a.id, 
        a.balance as main_balance, 
     b.summ  as sub_account_sum, 
        a.balance - b.summ as diff
    from accounts a 
    inner join sum_addi b on a.account_id = b.id
where balance <> b.summ;

Upvotes: 0

Prosenjit
Prosenjit

Reputation: 3

You can also use functions as below. I've kept same format as your SQL,just removed multiple sub queries and kept them in function. Logic is same as your SQL.

 WITH FUNCTION sa_acc(val VARCHAR2) RETURN VARCHAR2 IS
 lv_val varchar2(20);
 BEGIN
   SELECT SUM(amount) into lv_val
   FROM sub_account sa
   WHERE sa.account_id = val;

   return lv_val;
 END;


SELECT a.id,
       a.balance AS main_balance,
       sa_acc(a.id) AS sub_account_sum,
       a.balance - (sa_acc(a.id)) AS diff
FROM accounts a
INNER JOIN sub_account sa ON sa.account_id = a.id
WHERE a.balance != (sa_acc(a.id))
AND a.balance - (sa_acc(a.id)) < 5000
AND a.balance - (sa_acc(a.id)) >-5000
AND (
    SELECT
        COUNT(*)
    FROM
        sub_account sa
    WHERE
        sa.account_id = a.id
) > 1

Upvotes: 0

Koen Lostrie
Koen Lostrie

Reputation: 18685

Why does the WITH clause not work ? Give this a try. I removed the last statement (with the count(*) in the where clause, because you're doing an inner join so that condition will never be met anyway.

WITH sub_account_sum_by_acct AS
(select account_id, sum(amount) as sub_account_sum from sub_account sa group by account_id)
SELECT 
    a.id, 
    a.balance as main_balance, 
    sa.sub_account_sum, 
    a.balance - sa.sub_account_sum as diff
FROM accounts a 
     JOIN sub_account_sum_by_acct sa on sa.account_id = a.id
where a.balance != sa.sub_account_sum
and a.balance - sa.sub_account_sum < 5000
and a.balance - sa.sub_account_sum > - 5000

Upvotes: 2

GMB
GMB

Reputation: 222592

You could use window functions instead of subqueries:

select
    id,
    balance,
    sub_account_sum
from (
    select 
        a.id, 
        a.balance as main_balance, 
        sum(amount) over(partition by sa.account_id) as sub_account_sum,
        count(*) over(partition by sa.account_id) as sub_account_cnt
    from accounts a 
    inner join sub_account sa on sa.account_id = a.id
) t
where 
    balance != sub_account_sum
    and abs(a.balance - sub_account_sum) < 5000
    and sub_account_cnt > 1

Upvotes: 1

Related Questions