Getting differing count of same info

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Getting differing count of same info

Author

Topic

davebatt
Starting Member

17 Posts

Posted - 2010-06-15 : 09:01:39

Hello, imagine a table like

id / bit

I want to link my table to various others, and then get amn inital count of ids. I then in the following columns want a count of id where bit is true, and then false.

I have this

SELECT count(resultsets.resultsetid) as counter,

(SELECT count(resultsets.resultsetid)
FROM resultsets INNER JOIN
userDepartments ON resultsets.UserID = userDepartments.userID INNER JOIN
departments ON userDepartments.department = departments.deptID INNER JOIN
modules ON resultsets.moduleID = modules.ModuleID
WHERE ((@moduleID = 0) or (resultsets.moduleid = @moduleID)) AND
((@Department = 0) or (userdepartments.department = @Department)) AND
resultsets.completed = 1
GROUP BY departments.description,
modules.title,
resultsets.moduleID,
userDepartments.department) as completedCounter,

(SELECT count(resultsets.resultsetid)
FROM resultsets INNER JOIN
userDepartments ON resultsets.UserID = userDepartments.userID INNER JOIN
departments ON userDepartments.department = departments.deptID INNER JOIN
modules ON resultsets.moduleID = modules.ModuleID
WHERE ((@moduleID = 0) or (resultsets.moduleid = @moduleID)) AND
((@Department = 0) or (userdepartments.department = @Department)) AND
resultsets.completed = 0
GROUP BY departments.description,
modules.title,
resultsets.moduleID,
userDepartments.department) as IncompleteCounter,

AVG(resultsets.score) AS average,
departments.description,
modules.title,
resultsets.moduleID,
userDepartments.department
FROM resultsets INNER JOIN
userDepartments ON resultsets.UserID = userDepartments.userID INNER JOIN
departments ON userDepartments.department = departments.deptID INNER JOIN
modules ON resultsets.moduleID = modules.ModuleID
WHERE ((@moduleID = 0) or (resultsets.moduleid = @moduleID)) AND
((@Department = 0) or (userdepartments.department = @Department))
GROUP BY departments.description,
modules.title,
resultsets.moduleID,
userDepartments.department

And I get the error Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Can anyone help please

davebatt
Starting Member

17 Posts

Posted - 2010-06-15 : 09:41:07

Have no worries my learn-ed friends, I have foudn a solution:

SELECT count(resultsets.resultsetid) as counter,

SUM(CASE WHEN resultsets.completed = 1
THEN 1 ELSE 0 END) AS totalComplete,

SUM(CASE WHEN resultsets.completed = 0
THEN 1 ELSE 0 END) AS totalIncomplete,

AVG(resultsets.score) AS average,
departments.description,
modules.title,
resultsets.moduleID,
userDepartments.department
FROM resultsets INNER JOIN
userDepartments ON resultsets.UserID = userDepartments.userID INNER JOIN
departments ON userDepartments.department = departments.deptID INNER JOIN
modules ON resultsets.moduleID = modules.ModuleID
WHERE ((@moduleID = 0) or (resultsets.moduleid = @moduleID)) AND
((@Department = 0) or (userdepartments.department = @Department))
GROUP BY departments.description,
modules.title,
resultsets.moduleID,
userDepartments.department

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources