Ranking counts using an SQL query

Question

I am using DB Browser for SQLite. I have a table in the following format:

+-----------+-------------------------------------+
| search_id |             search_town             |
+-----------+-------------------------------------+
|         1 | town1,town3                         |
|         2 | town2,town4,town5                   |
|         3 | town3,town5                         |
|         4 | town2,town5                         |
|         5 | town2,town3,town4                   |
+-----------+-------------------------------------+

I would like to do a COUNT on the number of times town1 through town5 has appeared under search_town, and then rank in descending order the towns based on their respective counts. So far I have the following query:

SELECT SUM(CASE WHEN search_location LIKE '%town01%' THEN 1 ELSE 0 END) AS town01,
SUM(CASE WHEN search_location LIKE '%town02%' THEN 1 ELSE 0 END) AS town02,
SUM(CASE WHEN search_location LIKE '%town03%' THEN 1 ELSE 0 END) AS town03,
SUM(CASE WHEN search_location LIKE '%town04%' THEN 1 ELSE 0 END) AS town04,
SUM(CASE WHEN search_location LIKE '%town05%' THEN 1 ELSE 0 END) AS town05
FROM searches

...but am unable to do an ORDER BY as the towns and their counts are output as columns instead of rows in this format

+-------+-------+-------+-------+-------+
| town1 | town2 | town3 | town4 | town5 |
+-------+-------+-------+-------+-------+
|    12 |    31 |    12 |    24 |    12 |
+-------+-------+-------+-------+-------+

Is there another approach to this? Appreciate any comments.

Answer 1

You can use a recursive common-table expression (CTE) to turn the comma-separated list into a set of rows. When the table is normalized, you can group by town and sort by descending count:

WITH    rec(town, remain)
AS      (
        SELECT  SUBSTR(search_town, 0, INSTR(search_town, ','))  -- Before ,
        ,       SUBSTR(search_town, INSTR(search_town, ',')+1) || ','  -- After ,
        FROM    t1
        UNION ALL
        SELECT  SUBSTR(remain, 0, INSTR(remain, ','))  -- Before ,
        ,       SUBSTR(remain, INSTR(remain, ',')+1)  -- After ,
        FROM    rec
        WHERE   LENGTH(remain) > 0
        )
SELECT  town
,       COUNT(*)
FROM    rec
GROUP BY
        town
ORDER BY
        COUNT(*) DESC

Idea from this blog post. Working example at sqliteonline.

Answer 2

You are turning your output in a single row using CASE WHEN, to convert it into multiple rows, you can try like following.

;WITH cte 
     AS (SELECT * 
         FROM   (VALUES ('Town1'), 
                        ('Town2'), 
                        ('Town3'), 
                        ('Town4'), 
                        ('Town5')) T(town)) 
SELECT Count(*) [Count], 
       C.town 
FROM   [TABLE_NAME] T 
       INNER JOIN cte C 
               ON T.search_location LIKE '%' + C.town + '%' 
GROUP  BY C.town 
ORDER BY Count(*) DESC

Online DEMO

Another approach can be using UNION ALL.

SELECT * 
FROM   (SELECT Count(*) s, 
               'Town1'  AS Col 
        FROM   tablename 
        WHERE  search_location LIKE '%town1%' 
        UNION ALL 
        SELECT Count(*) s, 
               'Town2'  AS Col 
        FROM   tablename 
        WHERE  search_location LIKE '%town2%' 
        UNION ALL 
        SELECT Count(*) s, 
               'Town3'  AS Col 
        FROM   tablename 
        WHERE  search_location LIKE '%town3%' 
        UNION ALL 
        SELECT Count(*) s, 
               'Town4'  AS Col 
        FROM   tablename 
        WHERE  search_location LIKE '%town4%' 
        UNION ALL 
        SELECT Count(*) s, 
               'Town5'  AS Col 
        FROM   tablename 
        WHERE  search_location LIKE '%town5%') t 
ORDER  BY s DESC