Aggregating at Different Levels

  • 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 🙂


  • 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

  • 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