mySQL count similar row in two different tables

Question

I look around here and couldn't find answer and I can't seem to figure this out. What I'm look for is this:

I have two tables: Table 1:

+---------+---------------------+
| user_id | created_date        |
+---------+---------------------+
|       1 | 2013-05-17 11:59:59 |
|       1 | 2012-05-19 12:00:00 |
|       1 | 2014-06-11 12:00:02 |
|       1 | 2013-05-17 12:00:03 |
|       3 | 2014-01-12 14:05:00 |
|       3 | 2012-05-17 14:05:01 |
|       3 | 2013-05-17 15:30:00 |
+---------+---------------------+

Table 2:

+---------+---------------------+
| post_id | created_date        |
+---------+---------------------+
|       1 | 2013-05-17 11:59:59 |
|       1 | 2012-05-19 12:00:00 |
|       1 | 2014-06-11 12:00:02 |
|       1 | 2013-05-17 12:00:03 |
|       3 | 2014-01-12 14:05:00 |
|       3 | 2012-05-17 14:05:01 |
|       3 | 2013-05-17 15:30:00 |
+---------+---------------------+

I'm looking to count both the number of users and the number posts I have in the database for each month. The result should look like

+----------------+-------------+-------------+
| date           | users       | posts       |
+----------------+-------------+-------------+
| january 2013   | 27          | 10          |
| march 2013     | 108         | 101         |
| june 2013      | 270         | 100         |
| october 2013   | 2           | 1           |
+----------------+-------------+-------------+

I've started the mySQL query like this:

SELECT CONCAT_WS(' ', YEAR(u.date_created),MONTHNAME(u.date_created)) AS date,COUNT(*) AS users
FROM users AS u
UNION
SELECT CONCAT_WS(' ', YEAR(p.date_created),MONTHNAME(p.date_created)) AS date,COUNT(*) AS posts
FROM post AS p
GROUP BY YEAR(date_created),MONTHNAME(date_created)

But this only return two columns not three. Any help.

Thanks

fthiella · Accepted Answer · 2015-04-03 22:12:30Z

2

I think the easiest way is to count all users (and set the number of posts as NULL), then count all posts (and set the number of users to NULL), then combine the result of both queries using UNION ALL, and finally aggregate the counts using SUM() and a GROUP BY query:

SELECT
  `date`,
  SUM(users) AS users,
  SUM(posts) AS posts
FROM (
  SELECT
    DATE_FORMAT(date_created, '%M %Y') AS `date`,
    COUNT(*) AS users,
    NULL AS posts
  FROM
    users
  GROUP BY
    DATE_FORMAT(date_created, '%M %Y')

  UNION ALL

  SELECT
    DATE_FORMAT(date_created, '%M %Y') AS `date`,
    NULL AS users,
    COUNT(*) AS posts
  FROM
    posts
  GROUP BY
    DATE_FORMAT(date_created, '%M %Y')
) s
GROUP BY
  `date`

answered Apr 3, 2015 at 22:12

fthiella

49.2k15 gold badges96 silver badges108 bronze badges

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

Comments

Anthony · Accepted Answer · 2015-04-03 22:48:50Z

JOIN two derived tables using the common YEAR_MONTH value returned by EXTRACT:

SELECT 
    CONCAT_WS('-',
        YEAR(user_count.date_created), MONTHNAME(user_count.date_created)
    ) AS date,
    IFNULL(user_count,0) AS users,
    IFNULL(post_count,0) AS posts
FROM
    ( 
        SELECT 
        COUNT(user_id) AS user_count,
        EXTRACT(YEAR_MONTH FROM date_created) AS month_year,
        date_created
        FROM users
        GROUP BY month_year
  ) AS user_count
LEFT JOIN
    ( 
        SELECT COUNT(post_id) AS post_count,
        EXTRACT(YEAR_MONTH FROM date_created) AS month_year,
        date_created
        FROM posts
        GROUP BY month_year
    ) AS post_count 
ON post_count.month_year = user_count.month_year

Barmar · Accepted Answer · 2015-04-03 22:09:58Z

0

You need to join the two queries, not union them.

SELECT u.date, users, posts
FROM (
    SELECT CONCAT_WS(' ', YEAR(date_created),MONTHNAME(date_created)) AS date, COUNT(*) AS users
    FROM users
    GROUP BY date) AS u
JOIN (SELECT CONCAT_WS(' ', YEAR(p.date_created),MONTHNAME(p.date_created)) AS date, COUNT(*) AS posts
    FROM post
    GROUP BY date) AS p
ON u.date = p.date

Note that this will only return months that have both users and posts. If MySQL had FULL OUTER JOIN you could use this to get around it. If that's a problem, use fthiella's answer instead of this one.

answered Apr 3, 2015 at 22:09

Barmar

789k57 gold badges555 silver badges669 bronze badges

1 Comment

onlymushu Over a year ago

Yeah, I tried that but I it wasn't return the right thing. Thanks for the answer!

Lynne Davidson · Accepted Answer · 2015-04-03 22:11:58Z

0

You're using a union, which stacks both results in the same column. You need a join, with date as the joining field, and users and post from the two tables. Select date, users, posts from table1 t1 inner join table2 t2 on t1.date = t2.date.

answered Apr 3, 2015 at 22:11

Lynne Davidson

1831 gold badge1 silver badge14 bronze badges

Collectives™ on Stack Overflow

mySQL count similar row in two different tables

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related