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
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.