question

tiwaryrohit143 avatar image
tiwaryrohit143 asked

Query to calculate the new sum in same ratio as previous sum was distributed.

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.

t-sqlsql server 2012aggregation
3 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

where do you get the new sum? and, wouldn't you just throw out the old sum, so you would then have 21/700?

0 Likes 0 ·

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

0 Likes 0 ·
task.txt (354 B)

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

0 Likes 0 ·

0 Answers

· Write an Answer

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.