CODEWITHSUNDEEP
mysqlsqlcountmultiple-columns
Sašo Krajnc
Sašo Krajnc

Reputation: 133

Count multiple columns with same WHERE condition

I have a users table with columns: user_id, teacher_id1, teacher_id2, teacher_id3

and

teachers table with id

Each user can have the same id's for teacher_id1, teacher_id2, teacher_id3

I would like to count how many users have same teacher.

User table

+----------------------------------------+
| user_Id teacher_id1 teacher_id2 teacher_id3 |
+----------------------------------------+
| 1        1           1           1     |
| 2        2           1           3     |
| 3        2           3           3     |
| 4        2           2           2     |
+----------------------------------------+

Teacher table

+------+
| id   |
+------+
| 1    |
| 2    |
| 3    |

Count for $id1 is: 2

Count for $id2 is: 3

Count for $id3 is: 2

I tried something like this, but it is not correct!

SELECT COUNT(*) FROM users WHERE concat_ws('',teacher_id1 OR teacher_id2
OR teacher_id3) like '{$id}' ";

Upvotes: 0

Views: 1290

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269703

You have data in three different columns. You need to combine it into one column, to get the distinct counts that you want. For this, you can use union all. Then the count is simply count(distinct):

select teacher_id, COUNT(distinct USER_ID)
from ((select user_id, teacher_id1 as teacher_id
       from t
      ) union all
      (select user_id, teacher_id2
       from t
      ) union all
      (select user_id, teacher_id3
       from t
      )
     ) s
group by teacher_id;

Upvotes: 1

Meherzad
Meherzad

Reputation: 8553

Try this query 

select b.id, count(*) 
from
tbl1 a
inner join 
tbl2 b
on b.id = teacher_id1 or b.id = teacher_id2 or b.id = teacher_id3
group by b.id

SQL FIDDLE:

| ID | COUNT(*) |
-----------------
|  1 |        2 |
|  2 |        3 |
|  3 |        2 |

Upvotes: 0

Related Questions