Search code examples
sqlsqliteinner-join

SQL — how to count?

I have 2 tables:

users:
id | username
1  | alex
2  | max

orders:
id | user_id | status
1  | 1       | wait
2  | 1       | paid
3  | 2       | paid

What form should the SQL request take to take next?

username | wait_orders | paid_orders
alex     | 1           | 1
max      | 0           | 1
Solution

  • You join here using an INNER JOIN and then count your specific order statuses with a CASE statement and aggregate with SUM() and a GROUP BY:

    SELECT
    users.username
    SUM(CASE WHEN orders.status='Wait' THEN 1 ELSE 0 END) as wait_orders,
    SUM(CASE WHEN orders.status='Paid' THEN 1 ELSE 0 END) as paid_orders
FROM users
    INNER JOIN orders ON
        users.id = orders.user_id
GROUP BY username