Include null values in GROUP BY statement

  • Thread starter Thread starter jav
  • Start date Start date
J

jav

I have two tables: PRODUCT and CATEGORY

PRODUCT has:

product_id
name
amount
category_id

VALUES:

1, wine, 23, 2
2, rum, 12, 2
3, beer, 123, 2
4, mineral water, 210, 1

CATEGORY has:

category_id
name

VALUES:

1, non-alcoholic
2, alcoholic
3, snacks

I want to make an Access SQL query that return me:

category_id
total_amount

VALUES:

1, 210
2, 158
3, 0

I have this:

SELECT CATEGORY.category_id, SUM(PRODUCT.value)
FROM CATEGORY, PRODUCT
WHERE PRODUCT.category_id=CATEGORY.category_id

But this query doesn't return me the third category (snacks) because it
has null values. I want ir returns me the 0 value.

Thanks!!
 
Change the syntax of your query to use a LEFT JOIN

SELECT CATEGORY.category_id, SUM(PRODUCT.value)
FROM CATEGORY LEFT JOIN PRODUCT
ON PRODUCT.category_id=CATEGORY.category_id
 
Back
Top