CODEWITHSUNDEEP
mysql
Luciano Nascimento
Luciano Nascimento

Reputation: 2600

Mysql count(*) based in two relations

I would like to count(*) how much customers have created a post or made a comment. If the same customer has made several posts and comments, it should count only once.

Customer Table:
ID Name ...
1 Jonh
2 Mark
3 King
4 Doe

Post Table:
ID USER_ID...
1 1
2 1
3 3
4 1

Comment Table:
ID USER_ID...
1 1
2 3
3 3
4 4

It should return count(*) = 3
(user_id: 1, 3 and 4).

Upvotes: 0

Views: 64

Answers (2)

Highly Responsive
Highly Responsive

Reputation: 36

Try this one. It worked for me and returns what you're looking for:

SELECT COUNT( USER_ID ) AS TOTAL
FROM (
SELECT USER_ID
FROM POSTS
UNION
SELECT USER_ID
FROM COMMENTS
)X

I used POSTS and COMMENTS as table names bc I was unsure what your exact table names are, so make sure to change these in your query.

Upvotes: 2

Martin
Martin

Reputation: 219

This should work:

SELECT COUNT(DISTINCT USER_ID) FROM (
SELECT USER_ID FROM POST_TABLE 
UNION
SELECT USER_ID FROM COMMENT_TABLE
)

Upvotes: 1

Related Questions