CODEWITHSUNDEEP
sqlsql-server
Lifewithsun
Lifewithsun

Reputation: 998

SQL - Percentage Calculation

I want to calculate the percentage using a formula but it gives different result in SQL.

Query:

 DECLARE @totHappyVote BIGINT,
         @totNuetralVote BIGINT,
         @totUnHappyVote BIGINT,
         @happyIndex DECIMAL(5, 4)

DECLARE @happinessData TABLE (Id INT IDENTITY (1,1), FaceId INT)

INSERT INTO @happinessData (FaceId) 
VALUES (1), (1), (1), (2), (1), (1), (2), (2), (2), (1),
       (3), (1), (1), (3), (2), (2), (1), (1), (3), (1),
       (1), (3), (1), (2), (1), (1)

SELECT 
    @totHappyVote = (COUNT(CASE WHEN h.FaceId = 1 THEN 1 ELSE NULL END)),
    @totNuetralVote = (COUNT(CASE WHEN h.FaceId = 2 THEN 1 ELSE NULL END)),
    @totUnHappyVote = (COUNT(CASE WHEN h.FaceId = 3 THEN 1 ELSE NULL END))
FROM 
    @happinessData h

SELECT 
    @totHappyVote, @totNuetralVote, @totUnHappyVote

SELECT 
    'Result1'= CAST((100 * (2 * (@totHappyVote) + @totNuetralVote)) /
                  (2 * (@totHappyVote + @totNuetralVote + @totUnHappyVote)) AS NUMERIC (20, 3))

When I execute the query, it returns Result1 as 71.00; I want it to be the exact value of 71.15.

Upvotes: 0

Views: 114

Answers (2)

Lifewithsun
Lifewithsun

Reputation: 998

Adding exact solution as per answer provided by Gordon Linoff

select 'Result1'= CAST((100 *(2*(AVG(CASE WHEN h.FaceId=1 THEN 1.0 ELSE 0 END)) + AVG(CASE WHEN h.FaceId=2 THEN 1.0 ELSE 0 END))) /
(2 *(AVG(CASE WHEN h.FaceId=1 THEN 1.0 ELSE 0 END) +AVG(CASE WHEN h.FaceId=2 THEN 1.0 ELSE 0 END) + AVG(CASE WHEN h.FaceId=3 THEN 1.0 ELSE 0 END))) AS DECIMAL(5,2))
FROM @happinessData h

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

SQL Server does integer arithmetic so 1 / 2 is 0 rather than 0.5.

To fix this, just change the data type of your variables:

DECLARE @totHappyVote NUMERIC(20, 1);

Numerics don't do integer division.

Note that if you wanted the happy ratio, the simplest method is:

SELECT AVG(CASE WHEN h.FaceId = 1 THEN 1.0 ELSE 0 END) as happy_ratio
FROM @happinessData h;

This is the overall happy ratio -- number of happies relative to all the rows.

Upvotes: 3

Related Questions