CODEWITHSUNDEEP
sql
dave
dave

Reputation: 51

How can I get a count() in a join?

I have two tables. Call them jars and cookies. A cookie has one jar, a jar has zero or more cookies. The cookies table has a cookies.jar_id field for the reference.

What I want is a list of all jars that have cookies (excluding jars that have zero cookies), with a count of the number of cookies in each jar.

There may be millions of jars, and millions of cookies, so this is performance-sensitive. And 99% of jars have zero cookies, while those that have cookies may have hundreds of them, so I really need to exclude empty jars and not return a row for every cookie.

I'm sure this can be done somehow with a join, but I'm stumped.

Upvotes: 0

Views: 45

Answers (2)

NineBerry
NineBerry

Reputation: 28499

select jars.jar_id, count(*)
from jars
inner join cookies 
on cookies.jar_id = jars.jar_id
group by jars.jar_id

There should be an index on cookies.jar_id. Better even have a foreign key from cookies.jar_id to jars.jar_id

If you want to add other columns from the jars table to the result, use the Max() aggregate function:

select jars.jar_id, Max(jars.jar_name), count(*)
from jars
inner join cookies 
on cookies.jar_id = jars.jar_id
group by jars.jar_id

Upvotes: 4

Nir Kornfeld
Nir Kornfeld

Reputation: 827

You actually need to query the 'cookies' table only:

Select jar_id, count(*) as NumberOfCookies
From cookies
Where jar_id is not null
Group by jar_id

Upvotes: 5

Related Questions