What percentage of customers live between 2 & 5 miles from the HQ-CodePudding

I have a table of distances from headquarters in 2 dp. I am trying to find the percentage of customers within 2 and 5 miles.

SELECT COUNT(DISTANCE) AS COUNT 
FROM CUSTOMERS 
WHERE DISTANCE BETWEEN 2 AND 5

lets me know the number customer within the mile but i'm struggling to percentage, need help please.

SELECT (a.COUNT / COUNT(DISTANCE)) * 100 
FROM (
  SELECT COUNT(DISTANCE) AS COUNT 
  FROM CUSTOMERS 
  WHERE DISTANCE BETWEEN 2 AND 5 
) AS a, CUSTOMERS;

this is the query i wrote but its not working

CodePudding user response：

The simplest method may be to use conditional aggregation:

SELECT COUNT(CASE WHEN distance BETWEEN 2 AND 5 THEN 1 END)
       / COUNT(*)
       * 100 AS percentage
FROM   CUSTOMERS

Which would find the percentage of customers between 2 and 5 miles out of the total number of customers.

Or you can use:

SELECT COUNT(CASE WHEN distance BETWEEN 2 AND 5 THEN 1 END)
       / COUNT(distance)
       * 100 AS percentage
FROM   CUSTOMERS

Which would find the percentage of customers between 2 and 5 miles out of the total number of customers where their distance is known (non-NULL).