sql_count.md

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