Output is displayed as integer instead of float in SQL Server-CodePudding

I want to find the percentage of Married/Unmarried male and female employees.

This is the query:

SELECT
    MaritalStatus, Gender, 
    COUNT(*) AS "count",
    100 * COUNT(*) / (SELECT COUNT(*) FROM HumanResources.Employee) AS percentage
FROM
    HumanResources.Employee he
GROUP BY
    MaritalStatus,Gender

This is the output:

Correct values for 'percentage' column should be 16.89, 12.06, 33.44 and 37.58.

How can I get these values?

CodePudding user response：

Your problem is that you have integer division. You need to convert at least one side to decimal, either by multiplying by 100.0 or 1.0, or alternatively using CAST or CONVERT.

You can also improve your query by using a window function instead of a subquery. Note that the window function is calculated after aggregating, which is why you can put an aggregation function inside it SUM(COUNT(*)).

SELECT
    he.MaritalStatus,
    he.Gender, 
    COUNT(*) AS count,
    100 * CAST(COUNT(*) AS DECIMAL(9,2)) / SUM(COUNT(*)) OVER () AS percentage
FROM
    HumanResources.Employee he
GROUP BY
    he.MaritalStatus,
    he.Gender

CodePudding user response：

The ISO standard SQL language has a strong data typing. When compute formulae involves interger the result will always be an integr, and this happens in your case.

Some RDBMS does not apparently conform to this rule, like Oracle Database, because Oracle does not have any integer nor float datatypes only DECIMAL.

So you must use an implicit conversion of datatype to solve your problem or use a trick with a non interger constant to do so...

First form can be :

SELECT MaritalStatus, Gender, 
       COUNT(*) AS count,
       100.0 * COUNT(*) / SUM(COUNT(*)) OVER () AS percentage
FROM   HumanResources.Employee
GROUP  BY MaritalStatus, Gender

Second form can be :

SELECT MaritalStatus, Gender, 
       COUNT(*) AS count,
       CAST(100 AS DECIMAL (16, 3)) * COUNT(*) / SUM(COUNT(*)) OVER () AS percentage
FROM   HumanResources.Employee
GROUP  BY MaritalStatus, Gender

I'll prefer typing a constant not a column, for performances reasons !