Skip to content

Division with Aggregate Functions in SQL Not Behaving as Expected

I’m trying to do some crosstabs in SQL Server 2008 R2. That part is alright, however, if I try to get percentages for each cell, I run into a problem.

Here is a distilled use case: A survey where people give their favorite color and their favorite fruit. I’d like to know how many like a given fruit AND a given color:

Results:

What I was expecting:

Please help me to get what I was expecting?

Advertisement

Answer

You are using SQL Server. Here is a much simpler example that replicates the issue:

SQL Server does integer division.

Replace the denominator with something like sum(1.0) or sum(cast 1 as float) or sum(1e0) instead of sum(1).

Contrary to my expectation at least, SQL Server stores numbers with decimal points as numeric/decimal type (see here) rather than float. The fixed number of decimal spaces might affect subsequent operations.

User contributions licensed under: CC BY-SA
10 People found this is helpful