CODEWITHSUNDEEP
sqlpostgresqljoin
user2314197
user2314197

Reputation: 35

In PostgreSQL how do you join two tables whilst operating another function such as count

Following on from my prior question about joins I'm now having trouble with joins and comparing using count function.

I have a table called subjects

subno   subname   quota
30006   Math      300    
31445   Science   400 
31567   Business  250

I also have a another table called enrollment

subno sno   
30009 980008
4134  988880
31567 900890

etc. (Converted to SQLFiddle here: http://sqlfiddle.com/#!12/dcd01 -- Craig)

How do i List subject number and name which quota is less than the average quota of subjects. This means i need to count the number of students in one table and compare with the other table correct?

Upvotes: 0

Views: 140

Answers (2)

Anuj
Anuj

Reputation: 46

select a.subno,b.subname 
from
(select subno, count(sno) as cnt from enrollment
group by 1
having count(sno)<(select avg(quota) from subjects)
) as a 

inner join 

(select * from subjects) as b 
on a.subno=b.subno

Upvotes: 0

Bohemian
Bohemian

Reputation: 424983

After finally determining the question (deduced from comments) to be:

List all subjects with vacancies

The query you need is:

select
    subno,
    subname,
    quota,
    quota - count(sno) as vacancies
from subjects s
left join enrollments e on e.subno = s.subno
group by 1, 2, 3
having quota - count(sno) > 0

I also added in a column vacancies, which displays the number of vacancies remaining.

Note: You have misspelled "enrolments" (correct spelling has only one L) - I recommend you rename your table to the correct spelling to avoid future confusion.

Upvotes: 2

Related Questions