CODEWITHSUNDEEP
mysqlsql
Sergei Klinov
Sergei Klinov

Reputation: 798

How to make COUNT not count(fieldname) NULL values?

I'm setting up requests for e-commerce backend. I have 3 tables: product (with id as index), category (with id as index) and product_category, which binds first 2 tables, since one product can be in several categories and there can be several products in one category.

The request is to get a list of all categories containing names of categories and number of products in this category including zero values (when category contains no products). Last 2 columns of the results shown.

Unfortunatelly, COUNT(fieldname) gives me 1 instead of 0.

Here's my SQL request:

  SELECT product_category.id_category AS pr_cat_cat_id,
         id_product                   AS pr_cat_pr_id,
         product.name                 AS productname,
         categories.id,
         categories.name,
         COUNT ('pr_cat_cat_id')      AS quantity
    FROM product
         LEFT JOIN product_category ON product_category.id_product = product.id
         RIGHT JOIN (SELECT * FROM category) AS categories
             ON categories.id = product_category.id_category
GROUP BY name
ORDER BY id ASC

and get this result:

pr_cat_cat_id   pr_cat_pr_id    productname      id     name        quantity    
1               1               Product "Name1"  1      Category 1  2   
2               3               Product "Name 3" 2      Category 2  2   
NULL            NULL            NULL             3      Category 3  1   
NULL            NULL            NULL             4      Category 4  1   
NULL            NULL            NULL             5      Category 5  1   
NULL            NULL            NULL             6      Category 6  1

I do expect quantity to be zero on categories without products.

Upvotes: 0

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269803

You are counting a constant string value, which is never NULL. Use quotes correctly. You don't need them here:

COUNT(product_category.id_category) AS quantity

You cannot use an alias for the COUNT(). You need to refer to the original column.

Note that your query is malformed. The only things in the select should be name and the aggregation functions.

Upvotes: 2

jarlh
jarlh

Reputation: 44766

Column aliases from the same select list can't be referenced. Instead use its original column name:

COUNT(product_category.id_category)

Note: Single quotes are for string literals, and those are never null.

Upvotes: 0

Related Questions