Skip to content
Advertisement

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

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!

Advertisement

Answer

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

Example

Results

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement