How can I make groupwise count for each table and print them columnwise?

Question

Here I have two tables each have supervision field and user_id field. I want groupwise count for each supervision for both the table and print as below. both the tables contain different data.

Also count should be for specified user_id only.

Table1 columns

+----+-------------+---------+
| id | supervision | user_id |
+----+-------------+---------+
|  1 | type1       |       2 |
|  2 | type1       |       2 |
|  3 | type2       |      1  |
|  4 | type1       |       2 |
|  5 | type2       |       2 |
+----+-------------+---------+

Table2 columns

+----+-------------+---------+
| id | supervision | user_id |
+----+-------------+---------+
|  1 | type3       |       2 |
|  2 | type1       |       2 |
|  3 | type3       |       1 |
|  4 | type1       |       2 |
|  5 | type2       |       2 |
+----+-------------+---------+

For user_id=2 it should give output like this:

+-------+--------+--------+
| Type  | table1 | table2 |
+-------+--------+--------+
| type1 |      3 |      2 |
| type2 |      1 |      1 |
| type3 |      0 |      1 |
| ....  |        |        |
+-------+--------+--------+

For now this query I tried it gives correct result for table1 for table2 not.

select t1.supervision,
       count(t1.supervision) AS table1,
       count(t2.supervision) AS table2 from table1 t1 
LEFT JOIN 
table2 t2 ON t1.id=t2.id 
where t1.userid=2 AND t2.userid=2  
group by t1.supervision

MySQL or SQL Server? Please don't tag multiple database engines. — DavidG, Nov 13 '18 at 16:09
Is it possible to have some `type*` values, which exist in Table 1 only, and not in Table2, and vice versa being true as well ? — Madhur Bhaiya, Nov 13 '18 at 16:16

score 0 · Answer 1 · answered Nov 13 '18 at 16:21

SELECT t1.supervision, count(t1.id) AS table1, count(t2.id) AS table2
FROM users u
LEFT JOIN table1 t1 on (t1.user_id = u.id)
LEFT JOIN table2 t2 on (t2.user_id = u.id)
WHERE u.id = 2
GROUP BY t1.supervision

This assumes the users table exists. The problem you had was type3 did not exits in t1 so the left join would not have included values from there, and you were joining on id instead of user_id.

How can I make groupwise count for each table and print them columnwise?

1 Answers1