April 11, 2016 at 2:05 am
i have these tables :
Students
Scholarships
Students_Scholarships_junction
Defaulters
Defaulters_Scholarships_junction
And these are my two queries:
select scholarship_category , COUNT(s.scholarship_id) as [No of applicants]
from Students s
join Students_Scholarships_junction j on j.student_pk = s.student_pk
join Scholarships on j.scholarship_id = Scholarships.scholarship_id
where s.student_fullname is not null
group by scholarship_category
select scholarship_category ,COUNT (distinct student_fullname) as [No of duplicates]
from Defaulters
join Defaulters_Scholarships_junction j on j.Dstudent_pk = Defaulters .Dstudent_pk
join Scholarships on j.scholarship_id = Scholarships .scholarship_id
group by scholarship_category
i wish to combine these two queries such that the result will consist of three column
Scholarship category , No of applicants , No of duplicates
i have tried union and join but not getting it right
April 11, 2016 at 2:21 am
Something like this?
select
sc.scholarship_category
,COUNT(s.scholarship_id) as [No of applicants]
,COUNT (distinct d.student_fullname)
from Students s
join Students_Scholarships_junction ss on ss.student_pk = s.student_pk
join Scholarships sc on ss.scholarship_id = sc.scholarship_id
join Defaulters d on s.student_pk = d.Dstudent_pk
where s.student_fullname is not null
group by sc.scholarship_category
John
April 11, 2016 at 2:29 am
its showing correct value for 'no of duplicates' but not for 'no of applicants'. in fact its repeating the same value in both the fields.
April 11, 2016 at 6:58 am
More like this:
WITH Query1 AS(
select scholarship_category , COUNT(s.scholarship_id) as [No of applicants]
from Students s
join Students_Scholarships_junction j on j.student_pk = s.student_pk
join Scholarships on j.scholarship_id = Scholarships.scholarship_id
where s.student_fullname is not null
group by scholarship_category
),
Query2 AS(
select scholarship_category ,COUNT (distinct student_fullname) as [No of duplicates]
from Defaulters
join Defaulters_Scholarships_junction j on j.Dstudent_pk = Defaulters .Dstudent_pk
join Scholarships on j.scholarship_id = Scholarships .scholarship_id
group by scholarship_category
)
SELECT q1.scholarship_category, q1.[No of applicants], q2.[No of duplicates]
FROM Query1 q1
JOIN Query2 q2 ON q1.scholarship_category = q2.scholarship_category;
You might need to change the inner join to an outer join.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply