CODEWITHSUNDEEP
sql-servert-sql
CyberCube
CyberCube

Reputation: 169

Is there a better way of computing the weight

I need to calculate a column based on r1/Total Weight

DECLARE @total float(50)
SELECT @total=sum([Sales].[SalesOrderDetail].LineTotal) from [Sales]. 
[SalesOrderDetail]

Select r.LineTotal, r.LineTotal/@total as 'weight'
from [Sales].[SalesOrderDetail] r
order by LineTotal desc

Is there a more compact way of doing this?

Upvotes: 0

Views: 35

Answers (1)

forpas
forpas

Reputation: 164054

Simpler with a subquery that calculates the sum:

SELECT 
  LineTotal, 
  LineTotal / (SELECT SUM(LineTotal) FROM [Sales].[SalesOrderDetail]) AS weight
FROM [Sales].[SalesOrderDetail] 
ORDER BY LineTotal DESC

or with a CROSS JOIN:

SELECT 
  s.LineTotal, 
  s.LineTotal / t.SumLineTotal AS weight
FROM [Sales].[SalesOrderDetail] AS s CROSS JOIN (
  SELECT SUM(LineTotal) AS SumLineTotal 
  FROM [Sales].[SalesOrderDetail]
) AS t
ORDER BY s.LineTotal DESC

or with a CTE:

WITH cte AS  (
  SELECT SUM(LineTotal) AS SumLineTotal 
  FROM [Sales].[SalesOrderDetail]
)
SELECT 
  LineTotal, 
  LineTotal / (SELECT SumLineTotal FROM cte) AS weight
FROM [Sales].[SalesOrderDetail] 
ORDER BY LineTotal DESC

Upvotes: 2

Related Questions