CODEWITHSUNDEEP
mysqlsqlcount
Ryan
Ryan

Reputation: 119

Calculating fractions/percentages with SQL query

We have two tables with two columns each.

Table 1: Customers

+----+-------+
| Id | Name  |
+----+-------+
| 1  | Joe   |
| 2  | Henry |
| 3  | Sam   |
| 4  | Max   |
+----+-------+

Table 2: Orders

+----+------------+
| Id | CustomerId |
+----+------------+
| 1  | 3          |
| 2  | 1          |
+----+------------+

We want to calculate the percentage of customers who have made no orders.

The query to show the customers who have made no orders is:

SELECT name AS Customers FROM customers
    WHERE id NOT IN (SELECT customerid FROM orders);

This outputs:

+-----------+
| Customers |
+-----------+
| Henry     |
| Max       |
+-----------+

I thought that we could simply calculate the percentage by taking a count of the query above divided by a count of all names, which would give us an answer of 50%:

COUNT(SELECT name AS Customers FROM customers
    WHERE id NOT IN (SELECT customerid FROM orders)) /
SELECT COUNT(DISTINCT name) FROM customers;

This doesn't seem to be working. What's missing?

Upvotes: 1

Views: 8014

Answers (1)

sticky bit
sticky bit

Reputation: 37477

Get the count of distinct customer IDs from the order and divide that by the total count of customers. That will give you the fraction of customers who placed an order. Subtract that from 1 to get the fraction of customers who did not place an order. Multiply by 100 to get the fraction as percent.

SELECT (1
        - (SELECT count(DISTINCT customer_id)
                  FROM orders)
        / count(*))
       * 100
       FROM customers;

Upvotes: 1

Related Questions