SQL count products

I have table:

name    product
john     beer
john     milk
john     tea
john     beer
emily    milk
emily    milk
emily    tea
john     beer

i need select from this table, when output will be:

name count(tea)  count(beer) count(milk)  count(total)
john    1             3          1              5
emily   1             0          2              3

any idea how to do this?

DB: oracle 12

Answer

Use conditional aggregation:

select name
    sum(case when product = 'tea' then 1 else 0 end) cnt_tea,
    sum(case when product = 'beer' then 1 else 0 end) cnt_beer,
    sum(case when product = 'milk' then 1 else 0 end) cnt_milk,
    count(*) total
from mytable
group by name

Depending on your database, there may be neater options available to express the conditional counts.