select the words with the highest occurrence cross multiple columns

Question

i have the following table :

id  tag1     tag2    tag3
1   apple  orange   pears
2   orange  apple   pears
3   pears  orange   apple
4   orange  apple   pears
5   apple  orange   orange

I want to get this

tag      count
orange    6
apple     5
pears     4

I cannot make my query works

$res = mysql_query("SELECT tag, count(tag) occurrences
FROM
(
  SELECT col, tag
  FROM $tbl_name
  unpivot
  (
    tag
    for col in (tag1, tag2, tag3)
  ) unpiv
) d
GROUP BY tag 
order by occurrences desc");

It basically output nothing....there is an error somewhere.

`there is an error somewhere` ?? which error ? – Ameya Deshpande Feb 04 '15 at 10:20 — Ameya Deshpande, Feb 04 '15 at 10:20

score 0 · Answer 1 · edited May 23 '17 at 12:28

There's no pivot, or unpivot function in MySQL. However, you can use a union, similar to what was done here: MySQL - turn table into different table

Then you can just count the rows as follows:

$res = mysql_query(select f.tag, count(f.tag) occurrences
from (
select id, tag1 as tag
from fruit
union
select id, tag2 as tag
from fruit
union
select id, tag3 as tag
from fruit
  ) f
group by f.tag
order by occurrences desc);

Have a look at this SQL fiddle to have a look:

http://sqlfiddle.com/#!2/feb03/20

score 0 · Accepted Answer · answered Feb 04 '15 at 10:52

Similar to the suggestion by Reislef, but doing some of the summing within the sub query. This should (hopefully) mean the elements in the sub query can do the counting using an index, and greatly reduce the number of items coming from the sub query which need to be summed up outside the sub query without using indexes.

SELECT f.tag, SUM(f.tag_count) AS occurrences
FROM 
(
    SELECT tag1 AS tag, COUNT(*) AS tag_count
    FROM fruit
    GROUP BY tag
    UNION ALL
    SELECT tag2 AS tag, COUNT(*) AS tag_count
    FROM fruit
    GROUP BY tag
    UNION ALL
    SELECT tag3 AS tag, COUNT(*) AS tag_count
    FROM fruit
    GROUP BY tag
) f
GROUP BY f.tag
ORDER BY occurrences DESC

Note it is important to use UNION ALL (UNION would regard as duplicates to be eliminated any items where the count is the same in 2 or more columns)

select the words with the highest occurrence cross multiple columns

2 Answers2