select tc.UIDPK, count(torder.status) as total from TCUSTOMER tc inner join TORDER torder on tc.UIDPK=torder.CUSTOMER_UID where tc.UIDPK=490000;

The above query works fine for me. But the status can be IN_PROGRESS, FAILED, ON_HOLD

How do I write a query that returns a status count like tc.UIDPK, total orders, IN_PROGRESS orders, total orders-IN_PROGRESS orders. I tried the below but didn't work

select tc.UIDPK, count(torder.status) as total, count(torder2.status) as inprogress, count(torder.status)-count(torder2.status) as remaining from TCUSTOMER tc inner join TORDER torder on tc.UIDPK=torder.CUSTOMER_UID left join TORDER torder2 on tc.UIDPK=torder2.CUSTOMER_UID and torder2.status in('IN_PROGRESS') where tc.UIDPK=490000;