Selecting the frequency of a result that could appear in multiple columns (SQL)

Question

I have a table with a list of names spread across five different columns. I'm trying to get the 6 most frequent distinct names. Each name will only appear in each record once. The five columns are name_1, name_2...name_5. And just for names sake call the table 'mytable'.

Any help would be much appreciated.

Answer 1

Here's one approach:

SELECT name, COUNT(1)
  FROM (           SELECT name_1 AS name FROM mytable
         UNION ALL SELECT name_2 AS name FROM mytable
         UNION ALL SELECT name_3 AS name FROM mytable
         UNION ALL SELECT name_4 AS name FROM mytable
         UNION ALL SELECT name_5 AS name FROM mytable
       ) AS myunion
 GROUP BY name
 ORDER BY COUNT(1) DESC LIMIT 6
;

How many rows are there in the table?

Answer 2

try this:

SELECT iTable.iName, Count(iTable.iName) as TotalCount
FROM
(
    SELECT DISTINCT name_1 as iName FROM myTable
        UNION
    SELECT DISTINCT name_2 as iName FROM myTable
        UNION
    SELECT DISTINCT name_3 as iName FROM myTable
        UNION
    SELECT DISTINCT name_4 as iName FROM myTable
        UNION
    SELECT DISTINCT name_5 as iName FROM myTable
) as iTable
GROUP BY iTable.iName
ORDER BY TotalCount DESC
LIMIT 6

Answer 3

You should be able to select all the names from each table and union the results together. Then you can count the number of times each name occurs.

select * 
from 
(
  select name, count(*)
  from (
    select name from table1
    union all
    select name from table2
    union all 
    select name from table3
    union all
    select name from table4
    union all
    select name from table5
  )
  group by name 
  order by count(*) desc   
)
where rownum <= 6

Answer 4

UNION + subselect should work for you in this case.

SELECT name_1, COUNT(*) FROM (
    SELECT name_1 FROM mytable
    UNION ALL SELECT name_2 FROM mytable
    UNION ALL SELECT name_3 FROM mytable
    UNION ALL SELECT name_4 FROM mytable
    UNION ALL SELECT name_5 FROM mytable
) AS names GROUP BY name_1 ORDER BY 2 DESC LIMIT 6;