Post reply

Summing to get totals

  • December 15, 2005 at 4:50 am

    #92695

    Hi,

    can anyone help me with my problem, its driving me insane!

    Here's the scenerio. I have a table of product orders.

    ItemName Type Qty UnitPrice

    Toy.......A......7....£7.99

    Toy.......B......5....£5.99

    Toy.......C......5....£9.99

    Toy.......D......2....£3.49

    Toy.......E......1....£7.99

    I have to produce a report that shows the totals for a Toy.

    The correct result should be:

    ItemName Qty TotalPrice

    Toy......20......£150.80

    However, if I do something like: SELECT ItemName, SUM(Qty) as Qty, (SUM(Qty) * AVG(UnitPrice) as TotalPrice From Table Group By ItemName

    I end up with:

    ItemName Qty TotalPrice

    Toy.......20......£141.80

    I know its something to do with the averaging, I just cant figure it out.

    Any response gratefully appreciated.

    Andy

  • December 15, 2005 at 5:09 am

    #609690

    You are actually making things more complicated than they are by using AVG - that's only possible if you have the same Qty all over. Try this instead:

     

    declare @table table(ItemName varchar(10), Type char(1), Qty int, UnitPrice Decimal(5,2))

    insert @table select 'Toy', 'A', 7, 7.99

    insert @table select 'Toy', 'B', 5, 5.99

    insert @table select 'Toy', 'C', 5, 9.99

    insert @table select 'Toy', 'D', 2, 3.49

    insert @table select 'Toy', 'E', 1, 7.99

    select ItemName, sum(qty), sum(qty*UnitPrice) from @table group by ItemName

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply