Count all data even with zero/0 value

Question

I have data like this

product  location
snack    america
snack    brazil
biscuit  america
biscuit  china
biscuit  japan
soda     brazil
soda     japan
soda     india

what I want is like below, however I can do it differently using pivot, but is is possible to have the data like this?

location product count
america  snack   1 
america  biscuit 1
america  soda    0
brazil   snack   1 
brazil   biscuit 0
brazil   soda    1
japan    snack   0 
japan    biscuit 1
japan    soda    1

Answer 1

Please elaborate our concern, the result table that you are asking could be generated but specify your concern in a detail manner

Answer 2

We can generate a "calendar" table of all product/location combinations, via a cross join:

SELECT
    loc.location,
    prod.product,
    CASE WHEN t.location IS NULL THEN 0 ELSE 1 END AS count
FROM (SELECT DISTINCT location FROM yourTable) loc
CROSS JOIN (SELECT DISTINCT product FROM yourTable) prod
LEFT JOIN yourTable t
    ON t.location = loc.location AND
       t.product = prod.product
ORDER BY
    loc.location,
    prod.product;

Demo