April 27, 2012 at 4:02 am
It seems that my brain has eroded over the week, because I can't think how best to do this.
I am aggregating data from OrderHeader and OrderLines - my challenge is that I want to SUM data from both the OrderHeader and OrderLines tables in the same query.
The obvious problem is that anything I sum from OrderHeader will get summed for every order line, producing the wrong total. The following code demonstrates:
with Orders
as (select 1 Id,
45.99 OrderValue,
'2012-01-01' OrderDate
union
select 2,
45.99,
'2012-01-01'),
OrderLines
as (select 1 Id,
1.99 Price
union
select 1,
2.99
union
select 2,
3.99
union
select 2,
4.99)
select Orders.OrderDate,
TotalPrice = sum(OrderLines.Price),
TotalOrderValue = sum(orders.OrderValue)
from Orders
join OrderLines on Orders.Id = OrderLines.Id
group by Orders.OrderDate
In the above example, what is the best way to correctly calculate TotalOrderValue?
Thanks 🙂
April 27, 2012 at 4:33 am
Sum up your order lines before joining to Orders, something like this:
with Orders
as (select 1 Id,
45.99 OrderValue,
'2012-01-01' OrderDate
union
select 2,
45.99,
'2012-01-01'),
OrderLines
as (select 1 Id,
1.99 Price
union
select 1,
2.99
union
select 2,
3.99
union
select 2,
4.99),
OrderLinesTotal
as (select Id, sum(Price) Price
from OrderLines
group by Id)
select Orders.OrderDate,
TotalPrice = sum(OrderLinesTotal.Price),
TotalOrderValue = sum(orders.OrderValue)
from Orders
join OrderLinesTotal on Orders.Id = OrderLinesTotal.Id
group by Orders.OrderDate
John
April 27, 2012 at 4:37 am
Thank you very much John. One coffee later and I was just about to post back that I had worked it out along the same lines.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply