LostInTheSauce LostInTheSauce - 5 months ago 16
SQL Question

SQL COUNT GROUP BY multiple tables

I have 10 + tables that all have a column 'Error Message'. There is a unique ID for each Error Message. I need to write a script that returns the top 5 most recurring errors between all tables. Here is an example of the current structure along with the desired result.

Tables

Table 1
**Error Message
Mes 1
Mes 2
Mes 3

Table 2
**Error Message
Mes 1
Mes 2
Mes 3
Mes 4
Mes 4
Mes 4
Mes 4

Table 3
**Error Message
Mes 5
Mes 1
Mes 6
Mes 2


Desired Result

**Error Message Error Count**
Mes 4 4
Mes 1 3
Mes 2 3
Mes 3 2
Mes 5 1

Answer
select errMsg, count(*) as errCnt
from
(
    select errMsg from table1
    union all 
    select errMsg from table2
    union all 
    select errMsg from table3
    ...
) tmp
group by errMsg
order by count(*) desc

Depending on your DB engine add either limit 5 or top 5 or ROWNUM <= 5