SQL Statement for Listing with count

Question

I have a table called registrations from which I want to know the total count by city in the following layout.

Expected Layout

Tried preparing the below sql statement which gives me the correct information but not in the correct layout.

SELECT COUNT(IF(city LIKE '%location 1%',1, NULL)) 'LOCATION 1',
       COUNT(IF(city LIKE '%location 2%',1, NULL)) 'LOCATION 2',
       COUNT(IF(city LIKE '%location 3%',1, NULL)) 'LOCATION 3'
FROM registrations;

The layout from the above query.

Current Layout

How do I re-write the query to return the expected layout?

https://stackoverflow.com/questions/15745042/efficiently-convert-rows-to-columns-in-sql-server — BlackSwan, Apr 19 '19 at 13:04

score 2 · Accepted Answer · answered Apr 19 '19 at 13:04

2

You need to write the query as a UNION to get the results in the format you want:

SELECT 'LOCATION 1' AS location, COUNT(IF(city LIKE '%location 1%',1, NULL)) AS `count`
FROM registrations
UNION
SELECT 'LOCATION 2', COUNT(IF(city LIKE '%location 2%',1, NULL))
FROM registrations
UNION
SELECT 'LOCATION 3', COUNT(IF(city LIKE '%location 3%',1, NULL))
FROM registrations

answered Apr 19 '19 at 13:04

Nick

138,499
22
57
95

How could I automate this for future registrations from different cities? – h4kl0rd Apr 19 '19 at 13:18
@h4kl0rd are the city names exact matches? i.e. do you really need to use `LIKE`, or could you just use `=`? – Nick Apr 20 '19 at 01:22

score 1 · Answer 2 · answered Apr 19 '19 at 13:11

Use GROUP BY:

SELECT (CASE WHEN city LIKE '%location 1%' THEN 'LOCATION 1',
             WHEN city LIKE '%location 2%' THEN 'LOCATION 2',
             WHEN city LIKE '%location 3%' THEN 'LOCATION 3'
        END) as location,
       COUNT(*)
FROM registrations
GROUP BY location;

SQL Statement for Listing with count

2 Answers2