COUNT multiple columns and group by

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 2008 Forums

Transact-SQL (2008)

COUNT multiple columns and group by

Author

Topic

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2015-01-02 : 08:38:38

Having a little trouble getting this to work right.

Users are grouped, then a count of total open and total closed.
Open is StatusID IN (1, 2, 10)
Closed is StatusID = 3

Here is the starting point I have:


		SELECT u.FirstName + ' ' + u.LastName AS AssignedTo,
		COUNT(*) AS TotalClosed
		FROM Problems p
		LEFT OUTER JOIN Users u ON u.ID = p.AssignedToID
		WHERE p.statusid = 3
		AND p.AssignedToID <> 0
		GROUP BY u.FirstName, u.LastName

		SELECT u.FirstName + ' ' + u.LastName AS AssignedTo,
		COUNT(*) AS TotalOpen
		FROM Problems p
		LEFT OUTER JOIN Users u ON u.ID = p.AssignedToID
		WHERE p.statusid IN (1, 2, 10)
		AND p.AssignedToID <> 0
		GROUP BY u.FirstName, u.LastName

How can the above two queries be combined into one query?
I've been attempting a pivot but with no luck.
Thanks!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-01-02 : 10:09:48

Select count(case when p.status is =3 then 1 end) as count open,
... Same idea for closed

Zath
Constraint Violating Yak Guru

298 Posts

Posted - 2015-01-02 : 10:24:56

That did the trick. Should have thought about that.

Thanks!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources