CODEWITHSUNDEEP
sqlsql-servercount
undefined
undefined

Reputation: 479

SQL count 2 equal columns and select other columns

I have a two separate tables, one with vacancies, and one with applications to those vacancies. I want to select a new table which selects from the vacancies table with a number of other columns from that table, and another column that calculates how many applications there are for those vacancies. So my vacancy table looks like this:

ID  Active  StartDate   JobID   JobTypeID   HoursPerWeek
1   1       2017-02-28  2       CE          0
2   1       2017-02-15  4       CE          40
3   1       2017-02-14  1       CE          40
4   1       2017-02-28  1       CE          48

My applications table looks like this: 

ID  VacancyID   Forename    Surname     EmailAddress        TelephoneNumber
1   1           John        Smith       [email protected]    447777777777
2   2           John        Smith       [email protected]    447748772641
3   2           John        Smith       [email protected]    447777777777
4   2           John        Smith       [email protected]    447700123456
5   4           John        Smith       [email protected]    447400123569
6   4           John        Smith       [email protected]    447400126547
7   4           John        Smith       [email protected]    447555123654

I want a table that looks like this:

ID  Active  StartDate   JobID   HoursPerWeek    NumberOfApplicants
1   1       2017-02-28  2       0               1
2   1       2017-02-15  4       40              3
3   1       2017-02-14  1       40              0
4   1       2017-02-28  1       48              3

How can I select that table using joins and count the number of applicants where the VacancyID is equal to the ID of the first vacancy table? I have tried:

select Vacancy.ID, VacancyID, count(*) as NumberOfApplications from VacancyApplication
join Vacancy on Vacancy.ID=VacancyID
group by VacancyID, Vacancy.ID

This obviously doesn't select all the other columns and it also does not select ID 3 because there are 0 applications for that - I want ID 3 to be there with a value of 0 as well as all the other columns. How do I do this? I've tried various forms of grouping and selecting but I'm quite new to SQL so I'm not really sure how this can be done.

Upvotes: 0

Views: 58

Answers (4)

Gordon Linoff
Gordon Linoff

Reputation: 1270391

Use left join and table aliases:

select v.ID, count(va.VacancyID) as NumberOfApplications
from Vacancy v join
     VacancyApplication va
     on v.ID = va.VacancyID
group by v.ID;

You seem to want all the columns. You could include them in the group by. However, a correlated subquery or outer apply is simpler:

select v.*, va.cnt
from vacancy v outer apply
     (select count(*) as cnt
      from VacancyApplication va
      where v.ID = va.VacancyID
     ) va;

This is probably more efficient anyway, especially if you have an index on VacancyApplication(VacancyID).

Upvotes: 0

Zohar Peled
Zohar Peled

Reputation: 82504

You can use count as a window function using the OVER clause, thus eliminating he need for group by:

SELECT v.ID,
       v.Active,
       v.StartDate,
       v.JobID,
       v.JobTypeID,
       COUNT(va.ID) OVER(PARTITION BY v.ID) HoursPerWeek
FROM Vacancy v
LEFT JOIN vacancyapplication va ON(v.ID = va.VacancyID)

Upvotes: 0

Tajinder
Tajinder

Reputation: 2348

Hoping, i understood your problem correctly. Please try below query

select Vacancy.ID, VacancyID, count(*) as NumberOfApplications from           VacancyApplication
left join Vacancy on Vacancy.ID=VacancyID
group by VacancyID, Vacancy.ID

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93734

Use RIGHT JOIN instead of INNER JOIN and count the vacancyid column from vacancyapplication table. For the non matching records you will get count as 0

SELECT v.id, v.Active, v.StartDate, v.JobID, v.HoursPerWeek
       Count(va.vacancyid) AS NumberOfApplications 
FROM   vacancyapplication va 
       RIGHT JOIN vacancy v 
               ON v.id = va.vacancyid 
GROUP  BY v.id, v.Active, v.StartDate, v.JobID, v.HoursPerWeek

Start using Alias names, it makes the query more readable

Upvotes: 2

Related Questions