CODEWITHSUNDEEP
sqlsql-servert-sqlselectgroup-by
Arianule
Arianule

Reputation: 9063

Count for bit columns in select statement with group by

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

Answers (1)

gotqn
gotqn

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

Related Questions