CODEWITHSUNDEEP
sql-servert-sqlsql-server-2012subquery
Raed Alsaleh
Raed Alsaleh

Reputation: 1621

Optimize Nested Select query

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

Upvotes: 0

Views: 111

Answers (1)

neer
neer

Reputation: 4092

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

Upvotes: 1

Related Questions