Store sum for repeated use

• dopydb

  SSCertifiable

  Points: 6687

  More actions

  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 ?

• Mark Cowne

  One Orange Chip

  Points: 26979

  More actions

  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

• dopydb

  SSCertifiable

  Points: 6687

  More actions

  March 31, 2017 at 7:30 am

  #1935974

  thanks Mark great job! perfect for what i was after