Create a view with totals from multiple columns

Question

I need to create a view that displays various totals.

I have two tables. One with UserIDs and a status code of a job application. The other has the UserID and 4 columns that contain Boolean 0/1. Each of those columns indicate a job skill (like run, jump, throw, climb). The view just needs to show the totals for each job skill, once for applications in progress, and once for those that are completed. I'm having trouble figuring out the group by and selecting columns for each skill.

The User table has ID(int) and ApplicationStatus(varchar4).
The applicationStatus is:

• 0000 if it hasn't been started
• 0500 when it is in progress
• 1000 when finished.

The JobSkills table has an: ID, a matching UserID for the usertable, and 4 JobSkill columns: JobSkill_1, JobSkill_2, JobSkill_3 and JobSkill_4, each defined as an integer and contain a 1 if the user has that skill.
The view is supposed to show a summary of:

• how many users have status of 0000
• how many have status of 0500
• how many have status of 1000
• how many have status of 1000 and JobSkill_1 =1
• how many have status of 1000 and JobSkill_2 =1
• how many have status of 1000 and JobSkill_3 =1
• and how many have status of 1000 and JobSkill_4 =1

Something like:

SELECT 
    [UserID],
    SUM(JobSKill_1) as Run,
    SUM(JobSkill_2) AS Jump,
    SUM(JobSkill_3) AS Throw, 
    SUM(JobSkill_4) AS Climb
FROM [dbo].[tblJobSkills]
GROUP BY [UserID] WITH ROLLUP

Answer 1

It looks like you need conditional aggregation to achieve the results you need.

In SQL Server aggregate functions can take expression as the argument (for example SUM).

Therefore it is possible to have CASE WHEN expressions inside aggregate functions
SUM( CASE WHEN a = b THEN c ELSE d END )
where a, b, c, d can be columns, constants, functions or expressions (see CASE )

The above will cause CASE expression to be evaluated for each row and the result to be passed as input to SUM function.

Note: the above is different to CASE WHEN SUM() = a THEN b ELSE c END as in this case SUM function processes all input rows first and only results of the SUM function are compared.

Using the above approach it is possible to aggregate on a different set of conditions than what is specified in the GROUP BY.

The solution to your problem (assuming one to one relationship between User and JobSkills) is below:

SELECT SUM( CASE ApplicationStatus WHEN '0000' THEN 1 ELSE 0 END ) AS NotStartedCnt,
    SUM( CASE ApplicationStatus WHEN '0500' THEN 1 ELSE 0 END ) AS InProgressCnt,
    SUM( CASE ApplicationStatus WHEN '1000' THEN 1 ELSE 0 END ) AS CompletedCnt,
    SUM( CASE ApplicationStatus WHEN '1000' THEN JobSkill_1 ELSE 0 END ) AS JobSkill_1Count,
    SUM( CASE ApplicationStatus WHEN '1000' THEN JobSkill_2 ELSE 0 END ) AS JobSkill_2Count,
    SUM( CASE ApplicationStatus WHEN '1000' THEN JobSkill_3 ELSE 0 END ) AS JobSkill_3Count,
    SUM( CASE ApplicationStatus WHEN '1000' THEN JobSkill_4 ELSE 0 END ) AS JobSkill_4Count
FROM User AS U
    INNER JOIN JobSkills AS JS ON U.ID = JS.UserID