SQL Server Ratio SQL request with 2 decimal after comma

Question

I would like result with 2 decimal after comma and sum of RATIO_PERCENT equal 1.

This is my request:

SELECT T1.Matricule
, T1.mois
, T1.Contract
, T1.WorkPackage,SpentTime
, SUM(sum(T1.SpentTime)) OVER() as total
, 100.0 * SUM(T1.SpentTime) / SUM(sum(T1.SpentTime)) OVER() AS RATIO_POURCENT
FROM   Temp_ODdePaieWithoutGenius T1
GROUP  BY T1.Matricule, T1.mois, T1.Contract, T1.WorkPackage,SpentTime
;

And my result:

1   2015-01-01  test    WP1.2   1   178 0,561797752808989
1   2015-01-01  test1   WP1.3   30  178 16,8539325842697
1   2015-01-01  test1   WP1.4   2   178 1,12359550561798
1   2015-01-01  test1   WP1.5   2   178 1,12359550561798
1   2015-01-01  test1   WP1.6   2   178 1,12359550561798
1   2015-01-01  test1   WP3.1   10  178 5,61797752808989
1   2015-01-01  test1   WP4.1   20  178 11,2359550561798
1   2015-01-01  test1   WP5.1   52  178 29,2134831460674
1   2015-01-01  test2   WP2.1   2   178 1,12359550561798
1   2015-01-01  test2   WP2.2   6   178 3,37078651685393
1   2015-01-01  test2   WP2.3   8   178 4,49438202247191
1   2015-01-01  test2   WP2.4   2   178 1,12359550561798
1   2015-01-01  test2   WP2.5   4   178 2,24719101123596
1   2015-01-01  test2   WP2.6   9   178 5,0561797752809
1   2015-01-01  test2   WP2.7   3   178 1,68539325842697
1   2015-01-01  test2   WP2.8   6   178 3,37078651685393
1   2015-01-01  test2   WP3.1   2   178 1,12359550561798
1   2015-01-01  test2   WP6.1   5   178 2,80898876404494
1   2015-01-01  test3   WP1.1   10  178 5,61797752808989
1   2015-01-01  test3   WP2.9   2   178 1,12359550561798

With a simply ROUND(), the sum total is not equal to 1

ROUND(100.0 * SUM(T1.SpentTime) / SUM(sum(T1.SpentTime)) OVER())

This is a rather hard problem. Just put in a disclaimer that numbers may not add to 100% due to rounding. — Gordon Linoff, Dec 27 '16 at 15:06
You can achieve it accumulating percentages. Take a look at this [answer](http://stackoverflow.com/a/13483486/4357941) — David Isla, Dec 27 '16 at 15:12
So if you had three rows with 33.333333% what do you expect to happen? One of them arbitrarily is given a higher number than the others? — Martin Smith, Dec 27 '16 at 15:29
One thing is the value and other the presentation. For TOTALS sum the real value without ROUND, for presentation ROUND to 2 decimals. — Tito, Dec 27 '16 at 15:35

John Cappelletti · Answer 1 · 2016-12-27T15:46:00.610

Not tested, but here we will make the surgical adjustment to the largest value.

We just nested your original query (with the ROUND() ) and then perform final adjustment

Select Matricule
     , mois
     , Contract
     , WorkPackage
     , SpentTime
     , total
     , RATIO_POURCENT = RATIO_POURCENT + case when Row_Number() over (Order By RATIO_POURCENT Desc) = 1 then 100-sum(RATIO_POURCENT) over () else 0 end
 From  (
        SELECT T1.Matricule
        , T1.mois
        , T1.Contract
        , T1.WorkPackage,SpentTime
        , SUM(sum(T1.SpentTime)) OVER() as total
        , ROUND(100.0 * SUM(T1.SpentTime) / SUM(sum(T1.SpentTime)) OVER(),2) AS RATIO_POURCENT
        FROM   Temp_ODdePaieWithoutGenius T1
        GROUP  BY T1.Matricule, T1.mois, T1.Contract, T1.WorkPackage,SpentTime
       ) A

SQL Server Ratio SQL request with 2 decimal after comma

1 Answers1