CODEWITHSUNDEEP
mysql
AznDevil92
AznDevil92

Reputation: 554

COUNT two text values using LIKE - mysql

So I am trying to count the number of cities in my database that begins with Z and A in one query. From a few Googling searches, I came up with this query:

SELECT COUNT(CASE city WHEN 'Z%' then 1 else null end) as count_Z,
       COUNT(CASE city WHEN 'A%' then 1 else null end) as count_A
FROM city
WHERE city LIKE 'Z%' AND 'A%';

However, I am not getting any returns when I should have 19 rows returned for Z and 43 rows returned for A.

Can anyone help?

Upvotes: 1

Views: 47

Answers (2)

serakfalcon
serakfalcon

Reputation: 3531

I'm pretty sure the last line should be:

WHERE city LIKE 'Z%' OR city LIKE 'A%'

... Last time I checked it's not possible for a name to start with both Z and A.

Upvotes: 1

Cyclonecode
Cyclonecode

Reputation: 30051

I don't think you are able to use the following form Z% in your case statement. One way to achive this would be to use SUM() instead of case:

SELECT SUM(name LIKE 'A%') countA, 
       SUM(name LIKE 'Z%') countZ     
FROM city;

Note that you must use two individual LIKE statements when you check against two values. Consider the following table:

+------+------+
| id   | name |
+------+------+
|    1 | AAA  |
|    2 | ZZZ  |
|    3 | AAB  |
|    4 | ZZA  |
+------+------+

The following query:

SELECT * FROM <table> 
WHERE name LIKE 'A%' OR 'Z%';

Would return:

+------+------+
| id   | name |
+------+------+
|    1 | AAA  |
|    3 | AAB  |
+------+------+

Since you must use city LIKE 'A%' OR city LIKE 'Z%' for this to work as expected ie using two distinctive like statements.

Upvotes: 2

Related Questions