Get records greater than 1 for same status-CodePudding

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

CodePudding user response：

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.

CodePudding user response：

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