Post reply

Store sum for repeated use

  • March 31, 2017 at 3:21 am

    #104413

    hi 
    i have a simple query 

    select top 3 q.ID,TimeDate,q.Stockcode,FG_3_55g,FG_3_15g,FG_2_5g,FG_2g,FG_1_6g,FG_1g,[FG_710/500],FG_bp
    ,FG_3_55g + FG_3_15g + FG_2_5g + FG_2g + FG_1_6g + FG_1g + [FG_710/500]+ FG_bp as TotalFG
    from dbo.QCchecks q
    where TimeDate > GETDATE() -3
    order by ID desc

    i now want to use the FG_3_55g + FG_3_15g + FG_2_5g + FG_2g + FG_1_6g + FG_1g + [FG_710/500]+ FG_bp as TotalFG to find the %  of the individual values, eg
    (FG_3_55g / (FG_3_55g + FG_3_15g + FG_2_5g + FG_2g + FG_1_6g + FG_1g + [FG_710/500]+ FG_bp) )*100

    but is there a cleaner more efficient way of reusing the sum without using a function? eg i want to simply use
    (FG_3_55g / TotalFG) * 100
    (FG_3_15g / TotalFG) * 100

    anyone help me ?

  • March 31, 2017 at 3:50 am

    #1935930

    Use a cte

    with cte as (
    select top 3 q.ID,TimeDate,q.Stockcode,FG_3_55g,FG_3_15g,FG_2_5g,FG_2g,FG_1_6g,FG_1g,[FG_710/500],FG_bp
    ,FG_3_55g + FG_3_15g + FG_2_5g + FG_2g + FG_1_6g + FG_1g + [FG_710/500]+ FG_bp as TotalFG
    from dbo.QCchecks q
    where TimeDate > GETDATE() -3
    )
    select *,
    (FG_3_55g / TotalFG) * 100,
    (FG_3_15g / TotalFG) * 100
    from cte
    order by ID desc

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • March 31, 2017 at 7:30 am

    #1935974

    thanks Mark great job! perfect for what i was after

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

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