You have a database named MyDb. You run the following Transact-SQL statements:
A value of 1 in the IsActive column indicates that a user is active.
You need to create a count for active users in each role. If a role has no active users. You must display a zero
as the active users count.
Which Transact-SQL statement should you run?
A.
B.
C.
D.
wrong answer is n
Select
R.Rolename,
count(userID) as activeUsercount
from tblRoles R
Left join (select userID, RoleId from tblUsers Where Isactive = 1) u
On u.roleID = R.roleID
Group by R.roleID,R.rolename
4
4
Answer A is correct (see also question 4).
24
0
Agree
2
0
Agree with Peter.
Answer A is correct.
2
0
Hello Peter and E Rod,
I take your point, but how can a Count(*) give 0 back as result?
0
0
how is any answer correct as they dont cater to display zero?
0
0
my bad i see the bit data type now
0
0
Sample T-SQL script is here
https://1drv.ms/u/s!AqGoN4F5XgKMj45pg2yhZkknVsJkxg
10
0
Count(col) is counting over one column. returns 0 if the column hasn’t values
Count(*) is counting over all columns of a row. returns a value greater than 0 if there are rows (regardless of null values in its columns)
to count how many rows have value in one column Count(col) is the way
if you want to count if how many rows there are than Count(*) is the way
7
0
COUNT(*) counts all rows
COUNT(column) counts non-NULLs only
COUNT(1) is the same as COUNT(*) because 1 is a non-null expressions
0
0