SQL Aggregate Query - Count Nulls

I've got a database setup where we have a list of jobs. Each job is at 1 to many locations. A user can apply for a job, at any location. They can apply separately to the same job at different locations. Each job application has 4 statuses as it progresses, from 1. New through to 4. Closed.

I need to run a SQL query to present a summary of every job, at every location, and for every status a count of the relevant applications. So for example

+ ---- + -------- + ------ + ----- +
| Job  | Location | Status | Count |
+ ---- + -------- + ------ + ----- +
| 1000 | 1        | 1      | 7     |
| 1000 | 1        | 2      | 0     |
| 1000 | 1        | 3      | 1     |
| 1000 | 1        | 4      | 1     |
| 1000 | 2        | 1      | 4     |
| 1000 | 2        | 2      | 2     |
| 1000 | 2        | 3      | 0     |
| 1000 | 2        | 4      | 8     |
+ ---- + -------- + ------ + ----- +

In addition I'm ideally looking to sum the 4 status counts together into a total sum in the same list.

This is the SQL statement I've written so far:-

SELECT B.JobID, B.LocationID, B.ApplicationStatusID, COUNT(B.JobID) AS CountOfResults
FROM Job AS A
JOIN JobApplication AS B ON A.JobID = B.JobID
LEFT JOIN JobApplicationStatus AS C ON B.ApplicationStatusID = C.JobApplicationStatusID
WHERE A.BrandID = 1
GROUP BY B.JobID, B.LocationID, B.ApplicationStatusID

It works, bar two things:-

• I'm unsure how to get the total count (or could I do this server side later)
• It's not showing results where the count is 0.

Could anyone advise on how to get 4 status for every job at every location, even if that is 0? I've seen various advise on other threads about a left join, but I've had no success yet.

Thanks in advance!

Edit - Showing Job Table Example Data

Assuming Job Locations is a table of addresses with ID numbers 5, 6 and 7.

Job
+ ------ + ---------- + ------------------- +
| Job ID | Job Title  | Description         |
+ ------ + ---------- + ------------------- +
| 1      | Developer  | My Nice Description |
| 2      | Full Stack | Another Description |
+ ------ + ---------- + ------------------- +

Job Location
+ -- + ------ + ----------- +
| ID | Job ID | Location ID |
+ -- + ------ + ----------- +
| 1  | 1      | 5           |
| 2  | 1      | 6           |
| 3  | 2      | 5           |
| 4  | 2      | 6           |
| 5  | 2      | 7           |
+ -- + ------ + ----------- +

Job Application
+ -- + ------- + ------ + ----------- + --------------------- +
| ID | User ID | Job ID | Location ID | Application Status ID |
+ -- + ------- + ------ + ----------- + --------------------- +
| 1  | 1       | 1      | 5           | 1                     |
| 2  | 1       | 1      | 6           | 2                     |
| 3  | 2       | 1      | 5           | 1                     |
| 4  | 2       | 2      | 7           | 4                     |
+ -- + ------- + ------ + ----------- + --------------------- +

In the job application status table:-

• User 1 applies for Job 1 at Location 5, and their application status is 1.

• User 1 also applies for Job 1 at Location 6, and their application status is 2.

• User 2 applies for Job 1 at Location 5 and their application status is 1.

• User 2 also applies for Job 2 at Location 7 and their application status is 4.

What I am looking for here, would be a query to return the following:-

Result
+ --- + -------- + ------ + ----- +
| Job | Location | Status | Count |
+ --- + -------- + ------ + ----- +
| 1   | 5        | 1      | 2     |
| 1   | 5        | 2      | 0     |
| 1   | 5        | 3      | 0     |
| 1   | 5        | 4      | 0     |
| 1   | 6        | 1      | 0     |
| 1   | 6        | 2      | 1     |
| 1   | 6        | 3      | 0     |
| 1   | 6        | 4      | 0     |
+ --- + -------- + ------ + ----- +

And so on, 0's should be displayed. And the count is the count of job applications in that status number. There are 4 status numbers, which are set in a joining table called JobApplicationStatus.

---

You were almost there. Since you want all status even if there is no correspondence in Job Application, you need to use a cross join

Also, in this case, since there will be a "link" for every status, you need to use SUM with a CASE scenario instead of COUNT.

SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE Job
    ([JobID] int, [JobTitle] varchar(10), [Description] varchar(23), [BrandID] int)
;

INSERT INTO Job
    ([JobID], [JobTitle], [Description], [BrandID])
VALUES
    (1, 'Developer', 'My nice description', 1),
    (2, 'Full Stack', 'Another job description', 1)
;

CREATE TABLE JobApplication
    ([ID] int, [UserID] int, [JobID] int, [LocationID] int, [ApplicationStatusID] int)
;

INSERT INTO JobApplication
    ([ID], [UserID], [JobID], [LocationID], [ApplicationStatusID])
VALUES
    (1, 1, 1, 5, 1),
    (2, 1, 1, 6, 2),
    (3, 2, 1, 5, 1),
    (4, 2, 2, 7, 4)
;

CREATE TABLE JobApplicationStatus
    ([JobApplicationStatusID] int, [Description] varchar(11))
;

INSERT INTO JobApplicationStatus
    ([JobApplicationStatusID], [Description])
VALUES
    (1, 'New'),
    (2, 'In Progress'),
    (3, 'Hold'),
    (4, 'Closed')
;

Query 1:

SELECT B.JobID, B.LocationID, C.JobApplicationStatusID,
sum(case when B.ApplicationStatusID = C.JobApplicationStatusID then 1 else 0 end) AS CountOfResults
FROM Job AS A
JOIN JobApplication AS B ON A.JobID = B.JobID
cross join JobApplicationStatus AS C
WHERE A.BrandID = 1
GROUP BY B.JobID, B.LocationID, C.JobApplicationStatusID

Results:

| JobID | LocationID | JobApplicationStatusID | CountOfResults |
|-------|------------|------------------------|----------------|
|     1 |          5 |                      1 |              2 |
|     1 |          5 |                      2 |              0 |
|     1 |          5 |                      3 |              0 |
|     1 |          5 |                      4 |              0 |
|     1 |          6 |                      1 |              0 |
|     1 |          6 |                      2 |              1 |
|     1 |          6 |                      3 |              0 |
|     1 |          6 |                      4 |              0 |
|     2 |          7 |                      1 |              0 |
|     2 |          7 |                      2 |              0 |
|     2 |          7 |                      3 |              0 |
|     2 |          7 |                      4 |              1 |

EDITED :

There is a way to have job id that have no application. Just change the JOIN JobApplication to LEFT OUTER JOIN JobApplication and change B.JobID in the SELECT and the GROUP BY clause with A.JobID