-1

I have a problem to solve where I need to calculate a proportion in SQL (to two decimals). I have a column of ids, and I need the proportion of these ids that meet a certain criteria (WHERE...). It's been a while since I've worked with SQL, so I was curious if anyone could give some general advice. Thank you!

forpas
  • 160,666
  • 10
  • 38
  • 76
Cameron Zurmuhl
  • 1
  • I think you'll find the answer here: https://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement. Also, SO rewards posting your research, thoughts, attempts, and code, so consider adding that to your next question. – Aaron Bell Jan 28 '21 at 06:36
  • Thank you for the input. Since this was a homework question I wanted to keep it high level, but I understand. – Cameron Zurmuhl Jan 28 '21 at 15:54

1 Answers1

1

If you want the ratio of the ids that meet a condition like:

id > 100

or:

id > 100 AND id < 1000

or any other condition, you can use the condition directly inside the AVG() aggregate function:

SELECT ROUND(AVG(ID > 100), 2)
FROM tablename

or:

SELECT ROUND(AVG(ID > 100 AND id < 1000), 2)
FROM tablename

because SQLite evaluates the boolean expressions as 1 (true) or 0 (false).

This will return a floating point number between 0 and 1.

If you want the result as a percentage you can multiply by 100.

forpas
  • 160,666
  • 10
  • 38
  • 76