What's the simplest way to include the COUNT of associations in a SQL query?

Question

Say I have a [Products] table, and a [Products.record] can have many [Sales.records] associated with it.

I want to have a single dataset that gives me number of sales a product has. Of course a product can have zero sales.

The first version of my query looks like this:

SELECT products.*,COUNT(*) as num_of_sales
LEFT OUTER JOIN sales ON sales.product_id = products.id
GROUP BY products.id

The problem of course is that this query can't distinguish between Products that have had 1 sale and 0 sales.

So, how can I get this very simple dataset?

Answer 1

don't use COUNT(*) but instead, COUNT(sales.product_id).

SELECT  products.*,
        COUNT(sales.product_id) as num_of_sales
        LEFT OUTER JOIN sales 
            ON sales.product_id = products.id
GROUP   BY products.id

As a sidenote, you should specify all the non-aggregated columns in the GROUP BY clause.