Using a sub-select with a group by in SQL

I’m trying to get a sub-select query to return certain data. The below example is simplified for convenience:

Desired return:

fullname    | total | total_pending
John Smith  |     5 |             1
Jane Bloggs |     2 |             0

The data is in the following tables:

org_users: id, fullname orders: id, status, user_id

Status can be “pending” or “complete” on the order.

I’m writing my query like so:

SELECT 
    ou.fullname,
    COUNT(o.*),
    ( 
       SELECT COUNT(*) 
       FROM orders oo 
       INNER JOIN org_users ouu ON ouu.id = oo.user_id 
       WHERE oo.status = 'pending'
       AND ouu.id = oo.user_id
    ) as pending_orders
FROM orders o
INNER JOIN org_users ou on ou.id = o.user_id
GROUP BY ou.id

But this only returns this for me:

fullname    | total | total_pending
John Smith  |     5 |             7
Jane Bloggs |     2 |             7

How can I make my subselect tie to the user_id when it does its count? Been a while since I wrote raw SQL so I’m having trouble getting this right

Answer

Use conditional aggregation:

SELECT ou.fullname, COUNT(*),
       SUM(CASE WHEN o.status = 'pending' THEN 1 ELSE 0 END) as num_pending
FROM orders o INNER JOIN
     org_users ou 
     ON ou.id = o.user_id
GROUP BY ou.id, ou.fullname;