Optimize Nested Select query

Question

How to optimize the following query to be work better :

SELECT  c.a1,c.a2,          
            (SELECT SUM(t2.TempOB)  FROM tbl1 t2 WHERE    t2.AccNo LIKE CONCAT( C.AccNo,'%') )OB, 
            (SELECT SUM(t3.TempDebit) FROM tbl1 t3 WHERE  t3.AccNo LIKE CONCAT( C.AccNo,'%') ) Debit,
            (SELECT SUM(t4.TempCredit) FROM tbl1 t4 WHERE t4.AccNo  LIKE CONCAT( C.AccNo,'%') ) Credit
            FROM tbl1 C  WHERE AccLevel= @Level

Thorsten Kettner · Accepted Answer · 2016-10-24 12:18:43Z

1

You can use as the below:

SELECT  
    A.a1,
    A.a2,          
    SUM(B.OB) AS OB, 
    SUM(B.Debit) AS Debit,
    SUM(B.Credit) AS Credit
FROM 
    tbl1 A  LEFT JOIN 
    tbl1 B ON B.AccNo LIKE A.AccNo + '%'
WHERE 
    A.AccLevel = @Level 
GROUP BY
    A.a1,
    A.a2

edited Oct 24, 2016 at 12:18

Thorsten Kettner

96.6k8 gold badges56 silver badges82 bronze badges

answered Oct 24, 2016 at 11:56

neer

4,0926 gold badges22 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Optimize Nested Select query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related