SQL: How to Count Values in Different Columns

Question

I would like to know how to count values in different columns. Generic example:

ID - COLUNA1 - COLUNA2
1  - foo     - Bar
2  -         - Foo
3  - Qux     - Bar
4  - Bar     - 

I hope as a result

count - value
2     - Foo
3     - Bar
1     - Qux

Att, Alexandre.

Answer 1

To make this select you need first you will have to merge the two columns. You can use the command UNION ALL summing the results (as well as duplicates) by 2 or more.

After that, you need to count that selection using the HAVING. I also realize that you don’t want the fields that are empty so you can delete them from the count in the function HAVING, Thus:

SELECT  --seleção sobre o resultado do union
    COUNT(colunas) AS NUM_REG, 
    colunas AS SUM_COL
FROM
    (SELECT LOWER(COLUNA1) AS COLUNAS FROM TABELA   
     UNION ALL
     SELECT LOWER(COLUNA2) AS COLUNAS FROM TABELA
    ) nomeDoSelect --necessário nomear o union para funcinonar
GROUP BY
    colunas
HAVING COUNT(colunas) > 0 AND colunas != '' --excluindo células vazias

So you’ll get the result:

+---------+---------+
| NUM_REG | SUM_COL |
+---------+---------+
|    3    |   bar   |
|    2    |   foo   |
|    1    |   qux   |
+---------+---------+

Note that in the selection I made the UNION selections to be transformed to lowecase, because depending on the COLLATION of your database, the result may be different (ex of collation that needs to turn to the Ower: utf8_unicode_cs)