'Get records greater than 1 for same status

I have table logs

   id    status   
   ----------------
   SR123    20 
   SR123    19
   SR123    19  
   SR456    1
   SR456    2
   SR456    2

Table 2 status_master

     id         status
-------------------------
      1        Verify Email Success
      2        Email verification failed
      19       Send SMS Failed
      20       Send SMS Success

So, now I'm trying to get the retry count for each scenario. Expected result

status    count
----------------
19          1
1           1
sqlsql-server


Solution 1:[1]

Please be aware not to use keywords when naming the column like 'status' in your case.

You may need to use GROUP BY and COUNT for your problem

SELECT tt.id, COUNT(s.id) as count           
FROM log s INNER JOIN
     status_master tt
    ON tt.id = s.status
GROUP BY tt.id;

Here's fiddle for your problem: SqlFiddle

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions