CODEWITHSUNDEEP
mysql
onlymushu
onlymushu

Reputation: 113

mySQL count similar row in two different tables

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

Upvotes: 1

Views: 48

Answers (4)

Anthony
Anthony

Reputation: 37055

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

Upvotes: 1

fthiella
fthiella

Reputation: 49049

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`

Upvotes: 2

Lynne Davidson
Lynne Davidson

Reputation: 183

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.

Upvotes: 0

Barmar
Barmar

Reputation: 780852

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.

Upvotes: 0

Related Questions