Usage of COALESCE

Question

I have a query making use of COALESCE() to count the combination of 2 columns:

SELECT method, main_ingredient, COUNT(*) AS cnt FROM  `recipes` 
GROUP BY COALESCE( method, main_ingredient ) 

The result is useful. Sample result:

method    main_ingredient    cnt
================================
     1    4                  10
     2    1                  6
     3    6                  3
     4    6                  5
     5    2                  4
     6    8                  2

However, how can I obtain the results that has COUNT(*) equals to 0 ?

UPDATE with expected output:

method    main_ingredient    cnt
================================
     1    2                  0
     1    3                  0
     1    5                  0
     1    6                  0
     2    2                  0
     2    3                  0
                .
                .
                .
                .
               etc

UPDATE added the tbl_methods and tbl_main_ingredients:

Schema of tbl_methods:

id    method_name
=================
1     Method 1
2     Method 2
      .
      .
      .
6     Method 6

Schema of tbl_main_ingredients:

id     ingredient_name
======================
1      Ingredient 1
2      Ingredient 2
       .
       .
       .
8      Ingredient 8

Both id are the primary key of their table, auto-increment.

Answer 1

First you need to make a CROSS JOIN between tbl_methods and tbl_main_ingredients table in order to obtain the all possible combination of method and ingredient.

Later make a left join between the above cross joined table and your reipes table on matching method and main_ingredient.

Thus you will obtain a result for all possible combination of method and main_ingredient. If any combination exists in recipes table then you will get the corresponding count otherwise you will obtain 0 as count.

SELECT 
method_ingredients.method_id,
method_ingredients.ingredients_id,
COUNT(R.method) AS cnt
FROM 
(
    SELECT 
    TM.id AS method_id,
    TMI.id AS ingredients_id
    FROM tbl_methods TM
    CROSS JOIN tbl_main_ingredients TMI
) AS method_ingredients
LEFT JOIN `recipes` R ON R.method = method_ingredients.method_id AND R.main_ingredient = method_ingredients.ingredients_id
GROUP BY method_ingredients.method_id, method_ingredients.ingredients_id
ORDER BY method_ingredients.method_id, method_ingredients.ingredients_id;

Or

you can prefer the shorter version of this query:

SELECT 
 TM.id AS method_id,
 TMI.id AS ingredients_id,
 COUNT(R.method) AS cnt
FROM tbl_methods TM
CROSS JOIN tbl_main_ingredients TMI
LEFT JOIN `recipes` R ON R.method = TM.id AND R.main_ingredient = TMI.id
GROUP BY TM.id, TMI.id
ORDER BY TM.id, TMI.id;

More:

Some subtleties regarding COUNT:

SELECT COUNT(0);   Result: 1

SELECT COUNT(-1);  Result: 1

SELECT COUNT(NULL); Result: 0

SELECT COUNT(71); Result: 1

SQL FIDDLE

BTW there's nothing to do with COALESCE in your use case. COALESCE returns the first non-NULL element from the list if there's any otherwise NULL.

Example:

SELECT COALESCE(NULL,NULL,NULL,'abc',NULL,'def'); returns abc

SELECT COALESCE(NULL,NULL,NULL);  returns NULL