I have two tables
Table 'Sales Line' (SL)
Date "Entry No" Item Qty
(PK)
01/01/2018 1 ABC 1
01/02/2018 2 ABC 2
03/02/2018 3 DEF 1
04/06/2018 4 DEF 3
01/01/2019 5 DEF 1
06/06/2019 6 ABC 2
Table 'Cost Breakdown' (CB)
"SL Entry No" Cost
(FK)
1 10
1 15
2 5
3 25
4 10
4 10
5 5
6 5
6 10
Expected result :
Item Tot_Qty Tot_Cost
ABC 3 30
DEF 4 45
Note that I'm only interested on transaction in 2018 only.
How do I aggregate Tot_Qty and Tot_Cost ?
I've already tried with this query :
select sl.item, sum(sl.qty) tot_qty, sum(sl.qty * cb.cost) tot_cost
from sales_line sl
inner join cost_breakdown cb on cb.sl_entry_no = sl.entry_no
where sl.date >= '20180101' and sl.date < '20190101'
group by sl.item
... but it gave wrong result :
Item Tot_Qty Tot_Cost
ABC 4 30
DEF 7 45
( line from SL is being read repeatedly as many as correponding number of lines in CB)
January 6, 2021 at 10:22 am
Untested, but something like this. Use a CTE to pre-aggregate the values in Cost Breakdown and then JOIN to that from Sales Line.
With Cost_Breakdown_Totals as
(SELECT cb.sl_entry_no, tot_cost = sum(cb.cost) from cost_breakdown cb group by cb.sl_entry_no)
SELECT sl.item, total_cost = sum(ct.tot_cost * sl.qty)
FROM sales_line sl
JOIN Cost_Breakdown_Totals ct on sl.sl_entry_no = ct.sl_entry_no
where sl.date >= '20180101' and sl.date < '20190101'
group by sl.item
Phil, thank you for the hint regarding CTE
The final query that works:
With cte as
(SELECT cb.[sl entry no], tot_cost = sum(cb.cost) from cb group by cb.[sl entry no])
SELECT sl.item, total_qty = sum(sl.qty), tot_cost=sum(cte.tot_cost)
FROM sl
JOIN cte on sl.[entry no] = cte.[sl entry no]
where sl.date >= '20180101' and sl.date < '20190101'
group by sl.item
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply