MySQL: WHERE COUNT(*) = 0

Question

I am trying to get all the customer_id's where no rows from have been found, for example:

SELECT customer_id FROM transaction WHERE count(*) = '0'

I have tried this aswell:

SELECT customer_id, count(*) as total_rows FROM transaction WHERE total_rows='0'

But I get the error that total_rows is not a column.

possible duplicate of [MySQL - Using COUNT(\*) in the WHERE clause](http://stackoverflow.com/questions/301793/mysql-using-count-in-the-where-clause) — ztripez, Jun 03 '14 at 09:17
in query processing this is the logical order `From`>`where` since where is evaluated prior to select you get the error `that total_rows is not a column` — vhadalgi, Jun 03 '14 at 09:17
Both queries does not make sense, could you provide some sample data and expected output. — Abhik Chakraborty, Jun 03 '14 at 09:17
Check out `HAVING` clause ( http://dev.mysql.com/doc/refman/5.0/en/select.html ). But I have to say, your query doesn't look logical. If you have no entries in table, than you don't have any `customer_id` too. — Anpher, Jun 03 '14 at 09:18

score 3 · Accepted Answer · answered Jun 03 '14 at 09:17

The easiest way to do this is to think about it in a bit of a different way: "how do I get a list of all customers who have no transaction history?"

Simple! You get a list of all of the customers, join it against their transactions and filter out any customers who have a non-empty list of transactions. Or, in SQL:

SELECT 
    customer.customer_id 
FROM customer
LEFT JOIN transaction
    ON transaction.customer_id = customer.customer_id
WHERE
    transaction.transaction_id IS NULL

Note that you cannot simply use the transaction table like you're attempting. It is not a complete list of customer_id but rather it contains only IDs of customers who have an order.

Instead of operating on transaction and finding customers with no transactions (which you literally cannot do), you must find all customers and then filter by those who have no transactions. Similar concept, just opposite order.

MySQL: WHERE COUNT(*) = 0

1 Answers1