sql rounding

Question

I am executing below query on access and displaying 1(which i guess it is rounding)
how can i prevent rounding to show the actual results which is 0.036
SELECT
[Tbl1].[SumOf Amt]/
SUM( [Tbl1].[SumOf Amt]) as z
FROM [Tbl1]
group by [Tbl1].[SumOf Amt]

calculating
279.63/7692=0.036

Answer 1

No the reason you get 1 is not due to rounding. This is because you group by the amount and then compute the sum of that value. As long each value is unique, the result will be 1. If you have values that reappears, you get something else. Try this:

DROP TABLE IF EXISTS #temp
CREATE TABLE #temp (a float NOT NULL)
INSERT #temp (a) VALUES(16), (23), (23), (19), (19), (19)
SELECT a, a/SUM(a) FROM #temp GROUP BY a

Do you see the pattern?

I don't know exactly what you are looking for, but I think this is what you want:

SELECT [SumOf Amt] / SUM([SumOf Amt)] OVER()
FROM  tbl1

And, yes, there is no GROUP BY.

When you say SUM OVER(), you get the total sum for the result set.

Say that you have different accounts you for which would want to make this computation. Then you would do:

SELECT account, [SumOf Amt] / SUM([SumOf Amt)] OVER(PARTITION BY  account)
FROM  tbl1

The PARTITION BY clause says that you want the sum per account.

Answer 2

Try something like this:

select cast([SumOf Amt] as float) / sum([SumOf Amt]) over (order by (select null) rows between unbounded preceding and unbounded following) as z
from [Tbl1]

"Group by [SumOf Amt]" can be added too depending on your problem.

Answer 3

Hi @aswini aswini ，

Welcome to Microsoft Q&A!

If you would like to aggregate the total of each [SumOf Amt] but you also need to retain individual [SumOf Amt] in your final results. A SUM() on the [SumOf Amt] column would not allow you to get the correct totals because it would require a GROUP BY, therefore squashing the details because you wouldn't be able to keep the individual [SumOf Amt] in the select statement.

Normally we could use a #temp table, @table variable, or CTE filled with the sum of our data and grouped up so we could join to it again later to get a column of the sums we need. This could add processing time and extra lines of code.

Instead, we could use SUM OVER(PARTITION BY ()).

Please also refer below simple example:

create table [Tbl1]  
(  
[SumOf Amt] decimal(10,2))  
  
insert into [Tbl1]([SumOf Amt]) values  
(279.63),  
(1224.29),  
(6188.08)  
  
SELECT   
cast([SumOf Amt]/SUM([SumOf Amt]) over () AS DECIMAL(10,3)) as z  
FROM [Tbl1]  

Output:
z
0. 036
0. 159
0. 804

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.