Post reply

Is there a way to calculate percent of whole in one select statement

  • June 1, 2006 at 8:29 am

    #112432

    I need to calculte the percentage an items quantity is of the sum total for all items.  Is there a way in t-sql to nest or sum and calculate a percentage?  the basic statement I have been playing with is below and is not giving me the results that I expect.

    SELECT ItemID, ItemPct/(SELECT SUM(Qty) FROM tblItem)) FROM tblItem

     

    Thanks,

    vmon

  • June 1, 2006 at 8:49 am

    #641204

    Try this:

    select ItemId, (Qty/(select SUM(Qty) from tblItem where ItemId = i.ItemId)) * 100 as ItemPct

    from tblItem i

    Karl
    source control for SQL Server

  • June 1, 2006 at 9:48 am

    #641224

    I don't believe that will work.  Why do you want the sum(qty) for only one itemID?  Also, putting the subselect in the select statement is a bad idea because it gets executed for each row.

    You need to do the following:

    declare @tblitem table (id int, qty int)

    insert @tblitem values(1, 1)

    insert @tblitem values(2, 1)

    insert @tblitem values(3, 2)

    insert @tblitem values(4, 1)

    select i.id,  i.qty, (cast(i.qty as decimal) / t.total) * 100 as 'PerCent'

    from @tblitem i, (select sum(qty) total from @tblitem) t

  • June 1, 2006 at 11:24 am

    #641251

    Doh! You're absolutely right - don't know what I was thinking.

    Karl
    source control for SQL Server

Viewing 4 posts - 1 through 3 (of 3 total)

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