CODEWITHSUNDEEP
mysql
user1421214
user1421214

Reputation: 909

How to count number of distinct values when joining multiple tables?

I have following database tables 

categories
id, name

products
category_id, id, product_name, user_id

product_comments
product_id, id, comment_text, user_id

I need a count of number of different users in both products and product_comments tables. I have got the following query where I select all those categories where there is atleast one product and each product may have zero or some comments ... but what I can't figure out is that how to get the sum of these different user ids .. if it were just from one table I would try COUNT(products.user_id) ... .here is my query .. 

SELECT 
c.*
FROM categories c
INNER JOIN products p ON p.category_id = c.id
LEFT JOIN product_comments pc ON pc.product_id = p.id

I need total number of different users IDs from both products and product_comments tables.

I would expect the result data somewhat like below:

Category_id, Category_name, TotalUsers
1, Test Category, 10
2, Another Category, 5
3, Yet another cat, 3

Upvotes: 5

Views: 11239

Answers (4)

SteveP
SteveP

Reputation: 19093

If you want distinct users, then you could try something like:

select distinct user_id from (
   select user_id from products
   UNION
   select user_id from product_comments
) as allusers;

You can then count them:

select count(distinct user_id) from (
   select user_id from products
   UNION
   select user_id from product_comments
) as allusers;

Upvotes: 1

rcbevans
rcbevans

Reputation: 8892

You can use the DISTINCT keyword and COUNT() function

SELECT DISTINCT
COUNT(c.*)
FROM categories c
INNER JOIN products p ON p.category_id = c.id
LEFT JOIN product_comments pc ON pc.product_id = p.id

Upvotes: 0

Ed Gibbs
Ed Gibbs

Reputation: 26343

This will give you an overall count rather than a list of all distinct id's:

SELECT COUNT(DISTINCT user_id)
FROM products
LEFT JOIN product_comments comm ON products.id = comm.product_id

Upvotes: 7

Adam
Adam

Reputation: 480

select user_id from products
UNION
select user_id from product_comments

This will give you the distinct list of user ids that exist in the tables. The users could be present in just one of the tables, or both and will still be included in the list.

Upvotes: 0

Related Questions