CODEWITHSUNDEEP
sql
Anthony Kallay
Anthony Kallay

Reputation: 133

Improve SQL Query that contains multiple sub queries

I have an sql query scenario i have run into a few times, the output is correct but i was wondering if there is a better way to do it as it feels like i repeat the same formula to many times and run too many sub queries

 select s.StockId,

-- THESE ARE ALL REPEATED - CAN I GET ALL VALUES IN 1 QUERY?
isnull((select top 1 Cost from StockCosts sc where sc.Disabled = 0 and sc.StockId = s.StockId and ChargeType = 1),0) as VendorRecovery,
isnull((select top 1 Cost from StockCosts sc where sc.Disabled = 0 and sc.StockId = s.StockId and ChargeType = 2),0) as VendorCommission,

--THESE BOTTOM 3 ARE TO GET THE TOTALS, AGAIN REPEATED STATEMENTS?
(select SUM(Cost) from StockCosts sc where sc.Disabled = 0 and sc.StockId = s.StockId ) as VendorChargesNet,
(select (SUM(Cost) / 100) * st.VATRate from StockCosts sc where sc.Disabled = 0 and sc.StockId = s.StockId ) as VendorChargesVAT,
(select SUM(Cost) + (SUM(Cost) / 100) * st.VATRate from StockCosts sc where sc.Disabled = 0 and sc.StockId = s.StockId ) as VendorChargesTotal

from SaleTrans st 
inner join Stock s on st.StockId = s.StockId

Upvotes: 0

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

I would just use conditional aggregation:

select s.StockId,
       max(case when sc.ChargeType = 1 then sc.Cost else 0 end) as VendorRecovery,
       max(case when sc.ChargeType = 2 then sc.Cost else 0 end) as VendorCommission,
       sum(case when sc.Disabled = 0 then sc.Cost end) as VendorChargesNet,
       sum(case when sc.Disabled = 0 then sc.Cost / 100 end) * st.VATRate as VendorChargesVAT,
       sum(case when sc.Disabled = 0 then sc.Cost + (sc.Cost / 100) * st.VATRate end) as VendorChargesTotal
from Stock s join
     SaleTrans st 
     on st.StockId = s.StockId left join
     StockCosts sc
     on sc.StockId = s.StockId 
group by s.StockId, st.VATRate

Upvotes: 0

dnoeth
dnoeth

Reputation: 60482

A Correlated Scalar Subquery can usually be rewritten as an Outer Join.

And when you access the same table multiple times with the same condition you can probably combine them into a single Derived Table (or Common Table Expression).

This might return the same result:

select sc.*
from SaleTrans st 
inner join Stock s
left join 
 ( select
      min(case when ChargeType = 1 then cost end) as VendorRecovery -- or MAX?
     ,min(case when ChargeType = 2 then cost end) as VendorCommission
     ,SUM(Cost) as VendorChargesNet
     ,(SUM(Cost) / 100) * st.VATRate as VendorChargesVAT
     ,SUM(Cost) + (SUM(Cost) / 100) * st.VATRate as VendorChargesTotal
   from StockCosts sc 
   where sc.Disabled = 0
   group by sc.StockId
 ) as sc
on sc.StockId = s.StockId

Upvotes: 1

Related Questions