How to combine columns, group them then get a total count?-CodePudding

Below is a table that has candidate_id, two interviews they attended with the interviewer's name, and results for each interview.

candidate_id	interview_1	interview_2	result_1	result_2
1	Interviewer_A	Interviewer_B	Pass	Pass
2	Interviewer_C	Interviewer_D	Pass	Reject

I need help to combine column interview_1 and interview_2 into one column, and count how many pass and reject each interviewer gave to the candidate, the result I expected to see as below:

interviewer_name	pass_count	reject_count
Interviewer_A	1	0
Interviewer_B	1	0
Interviewer_C	1	0
Interviewer_D	0	1

SQL or Python either would work for me! Much appreciated!

CodePudding user response：

In SQL Server, it becomes a small matter for a CROSS APPLY

Example

Select [candidate_id]
      ,B.[Interview_name]
      ,pass_count   = case when result='Pass' then 1 else 0 end
      ,reject_count = case when result='Pass' then 0 else 1 end
 From YourTable A
 Cross Apply ( values ([interview_1],[result_1])
                     ,([interview_2],[result_2])
             ) B(Interview_name,result)

Results

candidate_id    Interview_name  pass_count  reject_count
1               Interviewer_A   1           0
1               Interviewer_B   1           0
2               Interviewer_C   1           0
2               Interviewer_D   0           1

CodePudding user response：

You could pull the dataset twice and union together, I'm assuming it's not an unwieldy set.

Something like -

WITH combined_set AS(
SELECT
 candidate_id,
 interviewer_1 as interviewer,
 result_1 as result
from candidate_table

UNION

SELECT
 candidate_id,
 interviewer_2 as interviewer,
 result_2 as result
from candidate_table)

SELECT
 interviewer,
 count(case when result = 'Pass' then 1 end) as pass_count,
 count(case when result = 'Reject' then 1 end) as reject_count
FROM combined_set