MySQL Query: Group by same column name from two different tables

Question

I have two tables: question and answer and both have the column created_date which defines the date they were created.

The question table:

mysql> describe question;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| id           | int(11)      | NO   | PRI | NULL    | auto_increment |
| created_date | datetime     | NO   |     | NULL    |                |
| user_id      | int(11)      | NO   | MUL | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

The answer table:

mysql> describe answer;
+--------------+------------+------+-----+---------+----------------+
| Field        | Type       | Null | Key | Default | Extra          |
+--------------+------------+------+-----+---------+----------------+
| id           | int(11)    | NO   | PRI | NULL    | auto_increment |
| created_date | datetime   | NO   |     | NULL    |                |
| question_id  | int(11)    | NO   | MUL | NULL    |                |
| user_id      | int(11)    | NO   | MUL | NULL    |                |
+--------------+------------+------+-----+---------+----------------+

What I want is to get some statistics about a user, meaning the number of questions and answers the user posted each day (or from a range of dates).

Example: For user with id 1 get me the number of questions and answers the user posted for the last 30 days (the records should be ordered chronologically).

The desired output would look something like this:

+---------------------+-----------+---------+
| date                | questions | answers |
+---------------------+-----------+---------+
| 2021-01-02          |         0 |       1 |
| 2021-01-03          |         5 |       5 |
| 2021-01-04          |         1 |       0 |
| 2021-01-05          |         1 |       0 |
| 2021-01-06          |         5 |       2 |
+---------------------+-----------+---------+

I have knowledge on SQL queries, but I believe for this type of query requires some kind of JOIN which I've never understood and did my best to avoid it.

What I've come up so far (for a user with id of 1):

SELECT q.created_date, COUNT(q.id) 
FROM question q, answer a 
WHERE q.id = a.question_id 
AND q.user_id = 1 
GROUP BY CAST(q.created_date AS DATE) 
ORDER BY q.created_date ASC;

Which results in:

+---------------------+-------------+
| created_date        | count(q.id) |
+---------------------+-------------+
| 2021-01-02 13:47:15 |           4 |
| 2021-02-09 13:24:52 |           1 |
| 2021-03-02 18:31:14 |          12 |
+---------------------+-------------+

A similar output should go for the answer table.

How do I merge the outputs together?

EDIT:

All dates and how many questions the user with id of 2 posted:

mysql> select cast(q.created_date as date) only_date, count(*) 
from question q 
where q.user_id = 2 
group by only_date;
+------------+----------+
| only_date  | count(*) |
+------------+----------+
| 2021-01-02 |        1 |
| 2021-02-10 |        1 |
| 2021-02-14 |        1 |
| 2021-03-16 |        1 |
| 2021-03-26 |        3 |
| 2021-03-27 |       23 |
| 2021-03-28 |        5 |
+------------+----------+

All dates and how many answers the user with id of 2 posted:

mysql> select cast(a.created_date as date) only_date, count(*) 
from answer a 
where a.user_id = 2 
group by only_date;
+------------+----------+
| only_date  | count(*) |
+------------+----------+
| 2021-02-08 |        2 |
| 2021-02-14 |        1 |
+------------+----------+

The desired output would be:

+------------+-----------+---------+
| only_date  | questions | answers |
+------------+-----------+---------+
| 2021-01-02 |         1 |       0 |
| 2021-02-08 |         2 |       0 |
| 2021-02-10 |         1 |       0 |
| 2021-02-14 |         1 |       1 |
| 2021-03-16 |         1 |       0 |
| 2021-03-26 |         3 |       0 |
| 2021-03-27 |        23 |       0 |
| 2021-03-28 |         5 |       0 |
+------------+-----------+---------+

Hi, @Akina. I actually meant to write it without the time part, so I would use something like CAST(created_date as DATE) to truncate the date. I don't actually need the time part, just the date. I updated the question. Thank you! — Gabi
– Gabi, Commented Mar 29, 2021 at 9:58

Akina · Accepted Answer · 2021-03-29 08:31:49Z

1

SELECT CAST(q.created_date AS DATE) created_date, 
       COUNT(DISTINCT q.id) questions,    -- only unique values are counted
       COUNT(a.id) answers     -- if multiple answers for the same question
                               -- are possible, add DISTINCT too
FROM question q
JOIN answer a ON q.id = a.question_id 
WHERE q.user_id = 1 
GROUP BY created_date       -- output column, not source table column, is used
ORDER BY created_date ASC;

answered Mar 29, 2021 at 8:31

Akina

43.2k6 gold badges17 silver badges30 bronze badges

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

Comments

Habie · Accepted Answer · 2021-03-29 08:36:34Z

1

U didnt tell us about the relation u have created between the tables which is very important! so i suggest u build Referential Integrity between the tables then it would be easy to generate report from both the tables...This is demo how u can create such relation

CREATE TABLE t1

( cid int NOT Null, name Varchar(30), index(cid), PRIMARY KEY(cid) )TYPE=INNODB

CREATE TABLE t2 ( tid int NOT Null, amount int,

cid int NOT Null,
PRIMARY KEY(tid),
index(cid),
FOREIGN KEY (cid) References t1(cid)

)

By doing this u can link these tables with the same ID...

answered Mar 29, 2021 at 8:36

Habie

525 bronze badges

1 Comment

Gabi Over a year ago

Hi, I'm sorry I did not specify this but the relation is: the answer table has the column question_id which is a foreign key for the id column from question table. So if I had to join them with a WHERE clause would be something like: WHERE answer.question_id = question.id.

Abdullah Tariq Abbasi · Accepted Answer · 2021-03-29 08:40:28Z

1

SELECT q.created_date, COUNT(q.id) 
FROM question q, answer a 
INNER JOIN question ON a.question_id = q.id
WHERE q.user_id = 1 
GROUP BY CAST(q.created_date AS DATE) 
ORDER BY q.created_date ASC;

answered Mar 29, 2021 at 8:40

Abdullah Tariq Abbasi

316 bronze badges

Comments

Nimantha · Accepted Answer · 2021-04-03 08:47:38Z

1

A query like this should help. This is not a working query. Just a psuedo. Please see. Will post a working query if possible.

select
    t1.user_id, t1.date, t1.q_count, t2.ans_count
from
    (
        select
        user_id, date, count(questions) q_count
        from
        question
        group by
        user_id, date
    ) t1
    left outer join
    (
        select
        user_id, date, count(questions) ans_count
        from
        question
        group by
        user_id, date
    ) t2 on t1.user_id = t2.user_id and t1.date = t2.date

edited Apr 3, 2021 at 8:47

Nimantha

6,5476 gold badges32 silver badges78 bronze badges

answered Mar 29, 2021 at 8:30

Praveen

4155 silver badges9 bronze badges

Collectives™ on Stack Overflow

MySQL Query: Group by same column name from two different tables

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related