inner join with group by and finding count of repeated values in a column

Question

I am using PostgreSQL databse.

I have the tables job, job_application, interview and their structure is as below :

job table:

select * from job;

job_application table

select * from job_application;

interview table:

select * from interview;

I have prepared a query to get some data by joining above tables something like below(of course, my actual requirement is slightly different and that I will explain later):

SELECT job_description,
       i1.interview_type,
       hired,
       open_positions - hired   AS remaining,
       Count(i1.interview_type) AS current_count
FROM   job
       INNER JOIN job_application j1
               ON job.id = j1.job_id
       INNER JOIN interview i1
               ON j1.id = i1.jobapplication_id
GROUP  BY i1.interview_type,
          job_description,
          hired,
          open_positions;

The above query will result data something like below:

But my actual requirement is to get the count of each interview_type(L1, L2, HR etc...from interview table) against the job_description. For example something like below:

Can anyone guide/explain me how to get the data in the above format from the query that I have already prepared? Please let me know if you need any information.

score 0 · Answer 1 · answered Sep 26 '21 at 14:44

You may have some luck with CrossTab (PostgreSQL Crosstab Query).

But I got there without it using:

SELECT 
    job.job_description
    ,job.hired
    ,job.open_positions - job.hired AS remaining
    ,COALESCE(L1.cnt, 0) AS L1
    ,COALESCE(L2.cnt, 0) AS L2
    ,COALESCE(HR.cnt, 0) AS HR
    ,COALESCE(EXECUTIVE.cnt, 0) AS EXECUTIVE
FROM job

LEFT JOIN
(
SELECT
    ja.job_id
    ,COUNT(*) AS cnt
FROM job_application ja

INNER JOIN interview i1
ON ja.id = i1.jobapplication_id
    
WHERE i1.interview_type = 'L1'

GROUP BY
    ja.job_id
) AS L1
ON L1.job_id = job.id

LEFT JOIN
(
SELECT
    ja.job_id
    ,COUNT(*) AS cnt
FROM job_application ja

INNER JOIN interview i1
ON ja.id = i1.jobapplication_id
    
WHERE i1.interview_type = 'L2'

GROUP BY
    ja.job_id
) AS L2
ON L2.job_id = job.id

LEFT JOIN
(
SELECT
    ja.job_id
    ,COUNT(*) AS cnt
FROM job_application ja

INNER JOIN interview i1
ON ja.id = i1.jobapplication_id
    
WHERE i1.interview_type = 'HR'

GROUP BY
    ja.job_id
) AS HR
ON HR.job_id = job.id

LEFT JOIN
(
SELECT
    ja.job_id
    ,COUNT(*) AS cnt
FROM job_application ja

INNER JOIN interview i1
ON ja.id = i1.jobapplication_id
    
WHERE i1.interview_type = 'EXECUTIVE'

GROUP BY
    ja.job_id
) AS EXECUTIVE
ON EXECUTIVE.job_id = job.id;

In other words doing a join to the SQL query to Job_application and interview tables and filtering to each interview type (which wouldn't be advisable if there were many more interview types).

inner join with group by and finding count of repeated values in a column

1 Answers1