Example

Below shows a forum web app. A post can only post by 1 user, its reply can reply by many users

Tables:

post

1
2
3
4
5
6
7
8
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| title             | varchar(128)     | NO   |     | NULL    |                |
| user_id           | int(10) unsigned | NO   |     | 0       |                |
| published_date    | datetime         | YES  |     | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+

reply

1
2
3
4
5
6
7
8
9
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| message           | varchar(128)     | NO   |     | NULL    |                |
| post_id           | int(10) unsigned | NO   |     | 0       |                |
| user_id           | int(10) unsigned | NO   |     | 0       |                |
| published_date    | datetime         | YES  |     | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+

user

1
2
3
4
5
6
7
+-------------------+------------------+------+-----+---------+----------------+
| Field             | Type             | Null | Key | Default | Extra          |
+-------------------+------------------+------+-----+---------+----------------+
| id                | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| name              | varchar(128)     | NO   |     | NULL    |                |
| email             | varchar(128)     | NO   |     | NULL    |                |
+-------------------+------------------+------+-----+---------+----------------+

Desired result

We want to know all topics total_users, unique_users, i.e.

1
2
3
+-------------------+------------------+--------------+----------------+
| Topic             | Total Users      | Unique Users | Published Date |
+-------------------+------------------+--------------+----------------+
1
2
3
4
5
6
7
SELECT topic.name AS topic_name
    , COUNT(reply.user_id) AS total_users
    , (SELECT COUNT(DISTINCT user_id) FROM reply WHERE topic_id = topic.id) AS unique_users
    , topic.published_date
FROM topic INNER JOIN reply ON topic.id = reply.topic_id
GROUP BY topic.id
ORDER BY unique_users

See the 3rd line of the query, we can select item from inner query and passing the value from outer query to it.