How do I do an inline view to force a 0 count

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)

How do I do an inline view to force a 0 count

Author

Topic

csphard
Posting Yak Master

113 Posts

Posted - 2007-06-14 : 12:27:42

I ask this yesterday but did not understand the answer. So I wanted to ask it again and simply my query.

Using the following statement I get counts for empid 153703 and 000212.
There are no items for 123123 so it does not show.

However I want to show a count of zero for it. I am not sure how to get it.

select count(empid),empid from emp_information_test where empid in ('153703','000212','123123')
group by empid

The answer I got
1 000212
1 153703

The answer I want
1 000212
1 153703
0 123123

Thanks for the help

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-14 : 13:02:46

Well, doing it that way, your number won't be returned if it's not in that table. Not knowing exactly what you're doing, one solution woul be to insert your values into a table variable. Then you could do a left join onto the actual table. If nothing exists for 123123, it will return null, which you could address with an isnull function

select sum(isnull(b.empid,0)),a.empid
from @tblVar a
left outer join emp_information_test b on a.empid = b.empid
group by empid

sshelper
Posting Yak Master

216 Posts

Posted - 2007-06-14 : 13:04:26

You can try it this way:

SELECT A.empid, ISNULL(B.empcount, 0)
FROM emp_information_test A LEFT OUTER JOIN (SELECT empid, count(empid) AS empcount
FROM emp_information_test WHERE empid in ('153703','000212','123123')) B
ON A.empid = B.empid
WHERE empid in ('153703','000212','123123')

SQL Server Helper
http://www.sql-server-helper.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources