How do I use the SQL COUNT() function properly in this context?

Question

I'm trying to create a SQL query that tells me how many users there are that have one or more products.

I created this query which tells me all of the users that have products:

SELECT DISTINCT `users`.*
FROM `users`
INNER JOIN `products`
ON `users`.`id` = `products`.`creator_id`

However, I want the count of the users, not the users themselves.

So I created this query which uses the COUNT() function

SELECT DISTINCT COUNT(*)
FROM `users`
INNER JOIN `products`
ON `users`.`id` = `products`.`creator_id`

But I believe I'm using the COUNT incorrectly because this query returns 164 which is a lot more than the previous query returned.

KrazzyNefarious · Accepted Answer · 2014-03-14 12:57:18Z

4

SELECT COUNT(DISTINCT `users`.`id`)
FROM `users`
INNER JOIN `products`
ON `users`.`id` = `products`.`creator_id`

answered Mar 14, 2014 at 12:57

KrazzyNefarious

3,2303 gold badges22 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rowland Shaw · Accepted Answer · 2014-03-14 13:03:40Z

1

DISTINCT filters your result rows to be unique. The aggregation (i.e. the COUNT()) is done prior, so is following your JOIN and counting users twice.

So, to get the number of users that have products, you could do:

SELECT      COUNT(DISTINCT u.id)

FROM        users    AS u

INNER JOIN  products AS p
    ON      u.id = p.creator_id

Similarly, you could do something a bit useful, like the count of products per user:

SELECT      u.id,
            COUNT(*) AS ProductCount

FROM        users    AS u

INNER JOIN  products AS p
    ON      u.id = p.creator_id

GROUP BY    u.id

answered Mar 14, 2014 at 13:03

Rowland Shaw

38.2k15 gold badges104 silver badges168 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2014-03-14 13:05:29Z

1

The following query may be more efficient:

SELECT COUNT(*)
FROM `users` u
WHERE exists (select 1 from `products` p where u.id = p.creator_id);

Performance will be best with an index on product(creator_id). And, performance will be better than the join/count(distinct) version when some users have many products.

answered Mar 14, 2014 at 13:05

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

1 Comment

Zane Over a year ago

Oh EXISTS you're so efficient and underutilized.

Collectives™ on Stack Overflow

How do I use the SQL COUNT() function properly in this context?

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related