CODEWITHSUNDEEP
mysqlsql
user7367328
user7367328

Reputation: 23

mysql queries display the same value within multiple fields

I not sure how to query out the same empID within this 5 fields of Emp_ID1 till Emp_ID5. Searching and count the same empID within those field of every rows.

------------------------------------------------------------
|Emp_ID1 | Emp_ID2 | Emp_ID3 | Emp_ID4 | Emp_ID5 | DATE
-------------------------------------------------------------
|   P1   |   P2    |    P3   |    P4   |    P5   | 2018-07-02
-------------------------------------------------------------   
|   P4   |   P3    |    P1   |    P8   |    P9   | 2018-07-03
-------------------------------------------------------------   
|   P5   |   P6    |    P4   |    P2   |    P1   | 2018-07-04
-------------------------------------------------------------   
|   P2   |   P3    |    P7   |    P8   |    P3   | 2018-07-04
-------------------------------------------------------------   
|   P5   |   P9    |    P3   |    P6   |    P1   | 2018-07-05
-------------------------------------------------------------
|   P5   |   P9    |    P3   |         |         | 2018-07-06
-------------------------------------------------------------

Date between 2018-07-02 and 2018-07-04

P1=3
P2=3
P3=4
P4=3
P5=2
P6=1
P7=1
P8=2
P9=1

Upvotes: 1

Views: 42

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

You could use union to merge data from different fields, and then use aggregation to get count for each employee id

select t.Emp_ID, count(*)
from(
  select Emp_ID1 Emp_ID  from your_table
  union all
  select Emp_ID2 Emp_ID from your_table
  union all
  select Emp_ID3 Emp_ID from your_table
  union all
  select Emp_ID4 Emp_ID from your_table
  union all
  select Emp_ID5 Emp_ID from your_table
) t
where t.Emp_ID is not null
group by t.Emp_ID

Demo

Upvotes: 1

Related Questions