CODEWITHSUNDEEP
mysqlsql
Ron
Ron

Reputation: 533

Group by like of a column value but may take each row to more than one group counter

I need to count how many hash tags (or other text) appearances I have in a table. A hash tag placed in a cell (column), but a cell may contains more than one hash tag. A cell contains up to one hash tag of each type.

Example inspired from that question but need to get differ result.

Lets say I have a table that similar to that:

+----+-----------------------+-------------+--+
| id |       Items           | timestamp1  |  |
+----+-----------------------+-------------+--+
|  1 | #Car #Dog #Fish       |  01/01/2018 |  |
|  2 | #Dog                  |  25/01/2018 |  |
|  3 | #Fish #Dog            |  18/03/2019 |  |
|  4 | #Car #Dog #Fish       |  23/06/2019 |  |
|  5 | #Bird                 |  17/10/2019 |  |
+----+-----------------------+-------------+--+

And I need to count how many times each item (#Car, #Dog, etc..) appear.

I have tried this 

SELECT (CASE WHEN items like '%#Dog%' THEN 'Dogs' 
             WHEN items like '%#Car%' THEN 'Cars'   
             WHEN items like '%#Fish%' THEN 'Fish'   
             WHEN items like '%#Bird%' THEN 'Birds'   
             END) as Item, count(*)
FROM  observations
GROUP BY (CASE WHEN items like '%#Dog%' THEN 'Dogs' 
               WHEN items like '%#Car%' THEN 'Cars'   
               WHEN items like '%#Fish%' THEN 'Fish'   
               WHEN items like '%#Bird%' THEN 'Birds'   
         END);

But this will give the (undesired) result:

+-------+----------+
| Item  | count(*) |
+-------+----------+
| Birds |        1 |
| Dogs  |        4 |
+-------+----------+

Desired results:

+-------+----------+
| count |   Item   |
+-------+----------+
|     4 | #Dog     |
|     2 | #Car     |
|     3 | #Fish    |
|     1 | #Bird    |
+-------+----------+

Is any good way to achieve that?

Upvotes: 1

Views: 69

Answers (2)

forpas
forpas

Reputation: 164099

If the items are known then use UNION ALL to get a query that returns them and then join to the table: 

SELECT t.Item, COUNT(o.id) counter
FROM (
  SELECT '#Car' AS Item UNION ALL SELECT '#Dog' UNION ALL
  SELECT '#Fish' UNION ALL SELECT '#Bird'
) AS t LEFT JOIN observations o
ON CONCAT(' ', o.Items, ' ') LIKE CONCAT('% ', t.Item, ' %')
GROUP BY t.Item;

See the demo.
Results:

| Item  | counter |
| ----- | ------- |
| #Car  | 2       |
| #Dog  | 4       |
| #Fish | 3       |
| #Bird | 1       |

Upvotes: 3

Strawberry
Strawberry

Reputation: 33945

I'm not seriously advocating this as a solution because the correct approach is to fix your data model. However, if this is a one-off problem, for a small data set...

I have a table of integers - ints(i) [0-9], but if using MySQL 8.0+, you can emulate this behaviour without the need for the utility table...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,items VARCHAR(120) NOT NULL
);

INSERT INTO my_table VALUES
(1,'#Car #Dog #Fish'),
(2,'#Dog'),
(3,'#Fish #Dog'),
(4,'#Car #Dog #Fish'),
(5,'#Bird');

SELECT n
     , COUNT(*) total 
  FROM 
     ( SELECT DISTINCT id
                     , TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(items,'#',i+1),'#',-1))n 
                  FROM my_table
                     , ints 
                 WHERE TRIM(SUBSTRING_INDEX(SUBSTRING_INDEX(items,'#',i+1),'#',-1)) <> ''
     ) a
 GROUP
    BY n;

+------+-------+
| n    | total |
+------+-------+
| Bird |     1 |
| Car  |     2 |
| Dog  |     4 |
| Fish |     3 |
+------+-------+

This assumes that a keyword only appears once per id or, at least, only wants to be counted once per id.

Upvotes: 1

Related Questions