Doing a count MySQL query?

Question

I have the following table:

UID | ID  | Type
1   | 1   | product
1   | 2   | product
1   | 3   | service
1   | 4   | product
1   | 5   | product
2   | 6   | service
1   | 7   | order
2   | 8   | invoice
2   | 9   | product

I want to end up with:

UID | product | service | invoice | order
1   |  4      |  1      |  0      |  1
2   |  1      |  1      |  1      |  0

What would the SQL query look like? Or at least, the most sufficient one?

Answer 1

If you really only need those four types, then you can hard-code the values as follows:

select UID,
    count(case when type='product' then 1 else null end) as product,
    count(case when type='service' then 1 else null end) as service,
    count(case when type='invoice' then 1 else null end) as invoice,
    count(case when type='order' then 1 else null end) as order
from MyTable
group by UID
order by UID