Create category column mysql

I have a transactions table,

Id customer_id order_id   transaction_date    status    vendor
1  BOB         TEST000001 2020-01-01          SHIPPED   Vendor BRO
2  JESS        TEST000002 2020-01-01          CANCELLED Vendor BRO
3  KAREN       TEST000003 2020-01-03          CANCELLED Vendor ALPHA
4  JESS        TEST000004 2020-02-04          CANCELLED Vendor CHARLIE

I want to query it using MySQL 5.7 group by vendor and count number of transaction, and categorize vendor by these criteria

1. Great: More than 2 SHIPPED and 0 CANCELLED transactions
2. OK: More than 2 SHIPPED and 1 or more CANCELLED transactions
3. Mediocre: other than Superb and Good criteria

result should be like this,

Vendor     Category Total Transaction
Vendor BRO GREAT    3

Answer

This is a case expression in an aggregation query:

select vendor, count(*) as total_transaction,
       (case when sum(status = 'SHIPPED') > 2 and sum(status = 'CANCELLED') = 0
             then 'Great'
             when sum(status = 'SHIPPED') > 2 and sum(status = 'CANCELLED') > 0
             then 'OK'
             else 'Mediocre'
        end) as status
from t
group by vendor;