Mysql – Using COUNT() and WHERE clause returns either zero or an empty set

Ultimately you’ll need to investigate to figure out what’s going wrong — we don’t have, and you probably can’t give us, enough information to figure it out for you — but I’ll provide a few explanations of what you’re seeing, and some pointers for next steps.

The first thing you have to recognize is that the table you’re querying has no rows where the state is Lagos and the phone_condition is Used. I realize that you’ve looked at a CSV file that seems to show such rows; but that still leaves a few possibilities, such as:

1. The CSV file that you’re looking at doesn’t actually correspond to the table you’re querying. (Maybe you’re querying the wrong table, or the right table but in the wrong database?)
2. At one point the CSV file and the table contained the same data, but since then one or both has been modified.
3. The table has rows where the state looks like Lagos, but actually isn’t: for example, maybe there’s whitespace or a control character that we can’t see visually, or maybe one of the characters that looks like a normal Latin character is actually a Cyrillic character or something. (See https://en.wikipedia.org/wiki/O_(Cyrillic).) Or, likewise for the phone condition.

When I tried select state, phone_condition from phones where state='lagos' and phone_condition='used'; it returned Empty set (0.01 sec)

Note that = is case-sensitive, so this is what you would have gotten even if there had been rows with the values Lagos and Used.

When I tried to check for null values among the columns using this query select count(*) from phones where state and phone_condition is null; it returned zero, meaning they’re not null values.

There are two mistakes here:

• Conceptually, there’s little reason to check for this. Even if there are rows that have null values in these fields, they wouldn’t be relevant to your desired query, which is looking for specific non-null values. (I’ll cover in a moment why your desired query showed nulls.)
• If you did want to check for this, you would need to write where state is null and phone_condition is null.

So, now, as to why your first query (select state, phone_condition, count(phone_condition) as lagosUsedphones from phones where ...) showed nulls . . .

• The query had to return a row, because it was selecting count(phone_condition), which means that if it didn’t find any rows matching the condition then it had to return a 0 for that count.
• Since MySQL didn’t find any rows matching the condition, it had to put something in the other returned fields. What it puts is NULL.
• Most database engines won’t even let you write a query like this; it doesn’t really make sense to mix aggregations like count(phone_condition) with regular fields like state, unless you’re using a GROUP BY clause to aggregate over specific values of that field. MySQL handles this by converting your regular fields like state to a sort of implicit aggregation that chooses an arbitrary value from the data — like how MIN and MAX are aggregations that choose the least and greatest values from the data, respectively, but this implicit aggregation just chooses some value from the data.

I think your next steps need to be running queries that help you understand what data your table does contain.

I would start by confirming that the table has roughly the data you expect, with queries like these:

SELECT state,
       phone_condition
  FROM phones
 LIMIT 10
;

SELECT COUNT(1),
       COUNT(state),
       COUNT(DISTINCT state),
       COUNT(phone_condition),
       COUNT(DISTINCT phone_condition)
  FROM phones
;

That second query also helps you determine how much data you’re working with, so that you know whether it’s feasible to look through all of it yourself, vs. whether you need to be cleverer with your queries.

If you find that there are a small enough number of distinct states that you can look through all of them, then I’d use this query to do so:

SELECT CONCAT('<', state, '>'),
       LENGTH(state),
       COUNT(1)
  FROM phones
 GROUP
    BY state
 ORDER
    BY state
;

LENGTH('Lagos') is 5, so any other value tells you that there are either extra characters or multi-byte characters. CONCAT('<', 'Lagos', '>') is <Lagos>; if you see any spaces after the < or before the >, then you’ll know the problem.

And likewise for phone conditions:

SELECT CONCAT('<', phone_condition, '>'),
       LENGTH(phone_condition),
       COUNT(1)
  FROM phones
 GROUP
    BY phone_condition
 ORDER
    BY phone_condition
;

If there are too many states or too many phone conditions for this, then you can reduce the result-set size by adding query conditions like WHERE phone_condition LIKE '%U%'. But be careful with this; any filtering you do has the risk of potentially removing rows that you’re genuinely interested in, because you don’t know exactly what’s wrong with the rows you’re genuinely interested in.

Edited to add:

Thanks it works. This is the query I used select state, phone_condition, count(phone_condition) as total from phones where state like '%Lagos%' and phone_condition like '%Used%';

OK, cool. So, yeah, that means that you have something extra before or after the Lagos or the Used. I think the most likely possibility is a trailing space after Lagos.