CODEWITHSUNDEEP
sqljoincountgroup-by
LostInTheSauce
LostInTheSauce

Reputation: 247

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

Upvotes: 0

Views: 47

Answers (1)

juergen d
juergen d

Reputation: 204766

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

Upvotes: 4

Related Questions