The difference between COUNT(*)
and COUNT(something)
in SQL
Use COUNT(something)
when you're counting an OUTER JOIN
. For example, counting the number of occurences of an association.
Given the following tables:
products
id | name | price |
---|---|---|
123 | salt | 2 |
456 | pepper | 3 |
sales
id | product_id | customer_id |
---|---|---|
1 | 123 | 789 |
There was 1 sale of salt (product
#123
), and 0 sales of pepper (product#456
).
-- count the number of sales for each product
SELECT
"product"."name",
COUNT(*) as "product_sales_count"
FROM
"products"
LEFT OUTER JOIN "sales" ON "sales"."product_id" = "product"."id"
GROUP BY
"products"."id"
Will return:
id | count |
---|---|
salt | 1 |
pepper | 1 |
-- count the number of sales for each product
SELECT
"product"."name",
COUNT("sales"."id") as "product_sales_count"
FROM
"products"
LEFT OUTER JOIN "sales" ON "sales"."product_id" = "product"."id"
GROUP BY
"products"."id"
Will return:
id | count |
---|---|
salt | 1 |
pepper | 0 |