SQL: How to count values in different columns

Question

SQL: How to count values in different columns

Navigation

#1 by (9 votes)

4

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 expect as a result

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

Att, Alexandre.

sql postgresql

asked by anonymous 11.01.2016 / 11:09

1 answer

What are the Try / Catch blocks? SQL Embedded in C #

score 9 · Accepted Answer

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

After that, you need to count this selection using HAVING . I also realize that you do not want fields that are empty so you can exclude them from the count in the HAVING function, like this:

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 have the result:

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

Note that in the selection I have made the UNION selections transformed to lowecase, because depending on the COLLATION of your database, the result may be different (ex collation than need to convert to lower: utf8_unicode_cs )