How to identify Records based on Status

Question

question

Grig asked May 26, '21

How to identify Records based on Status

I have a data set scenario where I want to find the user has picked the call only once ,irrespective of the the Time, it does not matter at when the user has picked the call once, either first time only, second time only or Nth time only.

If user has picked Call only once then I want to consider "Yes" against that Order No. Other wise "No". It can be possible that call attempts can be one or more than one against that Order No.

Note : ex :Order No: 44551 , only one attempt is made and the Status is Responsive, then against this order No. "No" should be there as only one attempt is made and which is Responsive.

Similarly Order No : 3456789, only one attempt is made and the Status is Not Responsive, then against this order No. "Yes" should be there as only one attempt is made and which is Not Responsive.

Based on the Status Column I want to check that what are the order Ids who have picked the call only Once irrespective of the attempts.

For example : Total 6 attempts were made against a particular Order No. and user didn't picked the call in first 4 times but picked the call 5th Time and again didn't picked the call in 6th Attempt as well ,so I want to consider as Yes in "Responded Only Once" Column which I need in the output

mysql sql2014

wwww.png (29.8 KiB)

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2021-05-27T11:37:52Z

harbingergroup answered May 27, '21

Just change tablename and column name in below query as per your schema.

select order_no, (case when count(order_no) = 1 then 'Yes' else 'No' End ) as 'responded only once' from mytable
where status = 'Responsive'
group by order_no

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question