I would like to create a sql query that reports the percentage of results in a particular range. for instance
20% of the values between 10 to 20
40% of the values between 20 to 32.5
Server - MSSQL
I would like to create a sql query that reports the percentage of results in a particular range. for instance
20% of the values between 10 to 20
40% of the values between 20 to 32.5
Server - MSSQL
8条答案
按热度按时间up9lanfz1#
You need to cover all possible values, hence the ELSE 0. You'll probably want to do something a little different there, but it should give you a start.
Based on Joel Gauvreau's comment:
Or at the end of the query use the
COMPUTE
statement.mccptt672#
w46czmvw3#
This will get you the count per range, you can easily determine the percentage from there:
vnjpjtjt4#
Joel's answer seems the best way to me. Posting to explain the query, and because the answer has an integer division sum/count which will return 1 or 0 instead of a percentage.
For the 20 -> 32.5 range:
The case returns 1 when the value is in range. Because there's no group by clause, the sum adds the result of the case for every row in the table. Convert to float, divide by the number of rows =count(*), and you get the percentage.
You can also write it like:
Here the CASE will result in a float 1.0 instead of the integer 1.
idv4meu85#
I would usually use a subquery and get rangecounts and join in the total to get the percentage. Something like:
9njqaruj6#
Little complicated, but that does work... i suppose...
dbo.Table_1 only has 1 column, 'id' and it is of type int.
pdkcd3nj7#
If it's something that you will be doing regularly, then you can create a table with the ranges and what constitutes those ranges. If not, you can set them up in a table variable or temporary table and join to that. It's basically JohnOpincar's solution, but with a table instead of a subquery.
Also, in your example you list "10 to 20" and "20 to 32.5". Where is a row counted if it's exactly 20? You should probably make sure that your requirements are clear on that point before you deliver the final solution.
polkgigr8#
Readers who found this question after a Google search are most likely, like me, wanting to create a continuous range.
Using the FLOOR() method, you can mathematically create this range with minimal code:
For the specific case of this question, where ranges are not continuous, the previous answer by Joel Coehoorn, using CASES, is the most adequate. It is easy to read and understand.