SQL query should return percentage but returns error-CodePudding

I have the following lines of code:

ROUND((((SUM(VALOR_2)) - SQLTMP.VALOR_1) / SQLTMP.VALOR_1) * 100, 2)

I was hoping it would return a percentage, but it returns an ERROR instead... Any ideas on what's wrong?

CodePudding user response：

Depending on your inputs you can try addapting this solution :

CREATE TABLE #TMP (
    val1 int,
    val2 int
);

INSERT INTO #TMP
VALUES (1,2),(1,3),(1,4),(2,5),(2,6)

GO
-- Your code begins here
WITH tmp_table AS (
    SELECT
        val1 AS val1,
        SUM(val2) AS sum_val2
    FROM #TMP
    GROUP BY val1,val2
)
SELECT ROUND((sum_val2 - val1)/val1,2) FROM tmp_table;
-- Your code ends here
GO

DROP TABLE #TMP

CodePudding user response：

select round(v1/(v2*1.0), 2) as pct from table -- multiplying by 1.0 converts the int to decimal, round limits it to 2 places.