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?

score 9 · Accepted Answer · answered May 17 '13 at 00:05

9

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.

answered May 17 '13 at 00:05

John Woo

258,903
69
498
492

+1 thanks JW. In your sidenote comment, why is that? And, would it suffice to state GROUP BY products.* ? – andy May 17 '13 at 00:22

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

1 Answers1