Suppose there are 2 columns name and num
Ram 21
Mohan 32
Rohit 11
Arjun 51 . Sum is 115. so ratio per person becomes like 21/115 and so on for others. now suppose i have a new sum 700 and now i want to distribute in same ratio like for Ram it will be ((21/115)*700 ).
Can i get a query where we can do this calculation.
Thanks in advance.
where do you get the new sum? and, wouldn't you just throw out the old sum, so you would then have 21/700?
Hi Kenj,
I am using below query
SELECT countydimid, employees , cast((employees*200) / (SELECT SUM(employees) FROM DataDateDim) as int) AS num2 FROM DataDateDim
but the thing is here i have to distribute 200 in same ratio. what happens after it is distributed the sum becomes less than 200 . suppose some rows are getting 0.435 it becomes 0 when casted to integer . Here the data is lost and sum becomes less than required.
i have attached the records here .task.txt
Thanks,
Rohit
Rather than INT use DECIMAL(6,4), change the scale and precision to get what you need.
https://docs.microsoft.com/en-us/sql/t-sql/data-types/decimal-and-numeric-transact-sql
21 People are following this question.
