Reputation: 9063
Struggling a little bit here.
I have 2 columns (selected and completed) of which I need to get the counts respectively.
Module Topic Selected Completed
Applying for finance Application process 0 NULL
Applying for finance Application outcome 1 NULL
Applying for finance Registration process 1 NULL
Applying for finance Insurance options 1 NULL
Applying for finance Repayment options 1 NULL
Applying for finance Application process 0 NULL
Applying for finance Application outcome 1 1
Applying for finance Registration process 1 1
Applying for finance Insurance options 1 1
Applying for finance Repayment options 1 1
How could I construct the select query to return as follow
Module Topic Selected Completed
Applying for finance Application process 0 0
Applying for finance Application outcome 2 1
Applying for finance Registration process 2 1
Applying for finance Insurance options 2 1
Applying for finance Repayment options 2 1
My approach has been something like this, but doesn't quite return the expected outcome.
select
m.Module,
t.Topic,
count (tu.Selected) as Selected,
count (tu.Completed) as Completed
from TopicUser tu
inner join Users u on u.Id = tu.UserId
inner join Topics t on tu.TopicId = t.TopicId
inner join Module m on m.ModuleId = t.ModuleId
group by m.Module, t.Topic, Completed
order by Module asc
Ideally the Topic columns should not repeat
Both the Selected and Completed columns are bit
Kind regards
Upvotes: 1
Views: 268
Reputation: 43676
Try this:
select
m.Module,
t.Topic,
count (tu.Selected) as Selected,
count (tu.Completed) as Completed
from TopicUser tu
inner join Users u on u.Id = tu.UserId
inner join Topics t on tu.TopicId = t.TopicId
inner join Module m on m.ModuleId = t.ModuleId
group by m.Module, t.Topic
order by Module asc
You have left
the Completed
column in the group by. We do not want to do this as the results are going to be grouped by it and more rows are going to returned.
Upvotes: 1