Question by tiwaryrohit143 · Feb 28, 2019 at 09:54 AM · t-sql sql server 2012 aggregation

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.

0 Show 3

10 |1200 characters needed characters left characters exceeded

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

KenJ · Feb 28, 2019 at 01:38 PM 0

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

tiwaryrohit143 · Feb 28, 2019 at 02:41 PM 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

task.txt (354 B)