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 

Answer

Sounds like an aggregation of an aggregation to me. Try:

SELECT status, [count] = SUM(retries)
FROM (
    SELECT status, retries = COUNT(*) - 1       
    FROM log
    GROUP BY id, status
    HAVING COUNT(*) >= 2
) G
GROUP BY status
ORDER BY status

Results

status	count
2	1
19	1

My results were different than your expected results. Did you mean for status = 1 to be status = 2 in your results?

See this db<>fiddle.