Using Id's in a Group by statement to count other data

Transact-SQL
1

I've grouped the data but I'd like to get Id's from each of these groups and use it to join other tables to get a count of items. I'm not getting anywhere with it!

More specifically I'm grouping Job reference, region and division (see below). These groups might have 1 or more JobId's. I need to get the number of Applicants at a given statusId (for example 310) who applied for these jobs. I've added a Select Count(applicants) column and thought I'd join it up but I get an error saying the j.jobid is not aggregate. It doesn't make sense to group by j.jobid.

Anyway this is the first step as then I'll have to dynamically create columns for each statusId!

SELECT
J.Reference,
RC.Region,
RC.Division,
>(SELECT COUNT(ja1.applicantid)
> FROM JobApps ja1
> INNER JOIN Jobs J1 on J1.JobId = Ja1.JobId and J.jobid = J1.JobId
> WHERE J.StatusId = 310) as [Sum of online new]
FROM JobApps JA
INNER JOIN Jobs J on J.JobId = JA.JobId
INNER JOIN Recruiters RC ON J.RecruiterId = RC.RecruiterId
group by J.reference, RC.Region, RC.Division

2

Hi

Are you still stuck with this issue

I would like to try and solve it ..

Please let me know

Thanks

3

I think you are looking for something like:

SUM(CASE WHEN StatusID = 310 THEN 1 ELSE 0 END)