How to perform division in SQL Server?

Question

My requirement is to divide the values in weightdiff by daydiff column in order to get the average daily gain. I don't know if it's possible since it in alias name. Kindly assist me.

This is the code that l had tried

SELECT 
CONVERT(varchar(20), Date, 107) as Date,
    Sum(Weight) as Weight,
    Sum(Weight) - LAG (Sum(Weight)) OVER (ORDER BY Date ) AS Weightdiff,
DATEDIFF(DAY, LAG(Date) OVER (ORDER BY Date), Date) AS Daydiff
FROM Store
Group By Date
ORDER BY Date Desc

Expected output

Date	Weight	Weightdiff	Daydiff	ADG
Jan 14, 2025	18	3	5	0.6
Jan 09, 2025	15	5	20	0.25
Dec 20, 2024	10	0	0	0

Answer 1

You could use a CTE to gain access to the alias values

WITH CTE as
(
SELECT 
CONVERT(varchar(20), Date, 107) as Date,
    Sum(Weight) as Weight,
    Sum(Weight) - LAG (Sum(Weight)) OVER (ORDER BY Date ) AS Weightdiff,
DATEDIFF(DAY, LAG(Date) OVER (ORDER BY Date), Date) AS Daydiff
FROM Store
Group By Date
)
SELECT *, 
  --divide the values in weightdiff by daydiff
 ROUND(CAST(Weightdiff as float) / NullIf(CAST(Daydiff as float), 0),2)  as [average daily gain]
FROM CTE
ORDER BY Date Desc  

fiddle

Date	Weight	Weightdiff	Daydiff	Average Daily Gain
Jan 04 2025	41	9	13	0.69
Jan 03 2025	32	7	3	2.33
Jan 02 2025	25	15	2	7.5
Jan 01 2025	10	null	null	null