- Home /

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.

Comment

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)

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

Copyright 2019 Redgate Software.
Privacy Policy

- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges