SQLize Online / PHPize Online  /  SQLtest Online

CREATE TABLE `transaction_detail` ( `id` int NOT NULL, `trx_id` int NULL, `product_name` varchar(100) NULL, `quantity` int NULL, `price` int NULL ); CREATE TABLE `transactions`( `id` int not null primary key, `customer_id` varchar (100) not null, `order_id` varchar (100) not null, `transaction_date` timestamp not null, `status` varchar(50) NOT NULL, `vendor` varchar(50) ); INSERT INTO transactions( id, customer_id, order_id, transaction_date, status, vendor ) values ( 1, '442818', 'TEST000001', '2018-01-01 00:00:10', 'SHIPPED', 'Vendor A' ), ( 2, '181820', 'TEST000001', '2018-01-01 00:10:10', 'SHIPPED', 'Vendor A' ), ( 3, '999019', 'TEST000001', '2018-01-02 03:18:01', 'CANCELLED', 'Vendor A' ), ( 4, '1923192', 'TEST000001', '2018-02-04 05:00:00', 'CANCELLED', 'Vendor C' ), ( 5, '645532', 'TEST000001', '2018-02-10 16:00:10', 'SHIPPED', 'Vendor C' ), ( 6, '1101011', 'TEST000001', '2018-02-11 11:00:11', 'SHIPPED', 'Vendor C' ), ( 7, '1020000', 'TEST000001', '2018-02-10 00:00:00', 'SHIPPED', 'Vendor D' ), ( 8, '40111234', 'TEST000001', '2018-03-11 06:30:11', 'SHIPPED', 'Vendor D' ), ( 9, '1923192', 'TEST000001', '2018-03-12 10:00:11', 'CANCELLED', 'Vendor B' ), ( 10, '1101011', 'TEST000001', '2018-03-12 15:30:12', 'SHIPPED', 'Vendor B' ), ( 11, '999019', 'TEST000001', '2018-03-15 12:30:45', 'CANCELLED', 'Vendor A' ), ( 12, '645532', 'TEST000001', '2018-04-01 09:30:22', 'SHIPPED', 'Vendor A' ), ( 13, '650013', 'TEST000001', '2018-04-01 10:50:37', 'SHIPPED', 'Vendor C' ), ( 14, '777734', 'TEST000001', '2018-04-02 13:45:19', 'SHIPPED', 'Vendor D' ); INSERT INTO transaction_detail( id, trx_id, product_name, quantity, price ) values (1, 1, 'Beng beng', 100, 6000), (2, 1, 'Taro', 80, 5500), (3, 2, 'Beng beng', 70, 6000), (4, 2, 'Taro', 41, 5500), ( 5, 2, 'Indomie Kari Ayam', 12, 3000 ), ( 6, 2, 'Indomie Ayam Bawang', 20, 3100 ), ( 7, 3, 'Indomie Ayam Bawang', 30, 3200 ), ( 8, 3, 'Indomie Kari Ayam', 90, 3300 ), (9, 3, 'Taro', 100, 5500), (10, 4, 'Beng beng', 40, 6000), ( 11, 5, 'Teh Sariwangi Murni', 50, 8000 ), ( 12, 6, 'Indomie Kari Ayam', 10, 3000 ), ( 13, 6, 'Indomie Ayam Bawang', 8, 3100 ), ( 14, 6, 'Teh Sariwangi Murni', 80, 8000 ), ( 15, 6, 'Teh Hijau Cap Kepala Djenggot', 15, 9500 ), (16, 7, 'Coki-coki', 70, 1000), (17, 8, 'Bakmi Mewah', 1500, 13000); select * from ( select vendor, case when count(t1."SHIPPED") > 2 and count(t2."CANCELLED") = 0 then 'Superb' when count(t1."SHIPPED") > 2 and count(t2."CANCELLED") > 1 then 'Good' else 'Normal' end "Category", count(t1."SHIPPED") + count(t2."CANCELLED") as "Total Transaction" from transactions t left join( select id, vendor as vendor1, status as "SHIPPED" from transactions t where status='SHIPPED') as t1 on t.id = t1.id left join ( select id, vendor as vendor2, status as "CANCELLED" from transactions t1 where status = 'CANCELLED') as t2 on t.id = t2.id group by vendor) as c order by case when c."Category" = 'Superb' then 1 when c."Category" = 'Normal' then 2 else 3 end, c."Total Transaction" desc