I want to run a query on two tables (jobs & jobs_applictions) and fetch total number of applications associated with each job.
Tables structure is:
jobs table
| job_id | job_title |
|---|---|
| 1 | System Analyst |
| 2 | Web Developer |
| 3 | Software Developer |
applications table
| app_id | job_id | application_date |
|---|---|---|
| 1 | 1 | 12-Jan-2023 |
| 2 | 1 | 12-Jan-2023 |
| 3 | 1 | 14-Jan-2023 |
| 4 | 2 | 14-Jan-2023 |
| 5 | 3 | 14-Jan-2023 |
| 6 | 3 | 15-Jan-2023 |
| 7 | 3 | 15-Jan-2023 |
| 8 | 3 | 15-Jan-2023 |
The expected result is:
| job_id | job_title | total_no_applications |
|---|---|---|
| 1 | System Analyst | 3 |
| 2 | Web Developer | 1 |
| 3 | Software Developer | 4 |
The query I was trying with some combinations COUNT DISTINCTIVE but I was unable to figure out and search on this platform did not fetch me the expected results.
CodePudding user response：
Using a join aggregation approach we can try:
SELECT j.job_id, j.job_title, COUNT(a.job_id) AS total_no_applications
FROM jobs j
LEFT JOIN applications a
ON a.job_id = j.job_id
GROUP BY 1, 2
ORDER BY 1;