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
Toggle Comment visibility. Current Visibility: Viewable by all users

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

KenJ avatar image KenJ ·

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 ·
tiwaryrohit143 avatar image tiwaryrohit143 ·

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)
WRBI avatar image WRBI tiwaryrohit143 ·

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.