to count value - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

to count value

Author

Topic

amalshah71
Starting Member

9 Posts

Posted - 2006-11-30 : 01:43:16

i have a table emp with 2 columns as

empid mgrid
1 1
2 1
3 2
4 3
5 4

now my aim is to find out that a empid appears how many times in mgrid

am a beginer
help appreciated

cheers

arvind
Starting Member

12 Posts

Posted - 2006-11-30 : 02:13:03

Select e1.empid,count(*) from emp e1 join emp e2 on e1.empid = e2.mgid where e1.empid = e2.mgid group by e1.empid

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-30 : 02:28:59

Correction:

Select e1.empid,count(*) 
from emp e1 join emp e2 
on e1.empid = e2.mgrid 
where e1.empid = e2.mgrid 
group by e1.empid

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-30 : 03:39:27

Both of the previous suggestions misses out empid #5.

--prepare test data
declare @t table (empid tinyint, mgrid tinyint)

insert	@t
select	1, 1 union all
select	2, 1 union all
select	3, 2 union all
select	4, 3 union all
select	5, 4

-- do the work

-- Query 1
select distinct	e1.empid,
		isnull(e2.cnt, 0) cnt
from		@t e1
left join	(
			select		mgrid,
					count(*) cnt
			from		@t
			group by	mgrid
		) e2 on e2.mgrid = e1.empid

-- Query 2
select distinct	e1.empid,
		(select count(*) from @t e2 where e2.mgrid = e1.empid) cnt
from		@t e1

-- Query 3
select		e1.empid,
		sum(case when e2.mgrid is null then 0 else 1 end) cnt
from		@t e1
left join	@t e2 on e2.mgrid = e1.empid
group by	e1.empid

-- Query 4
select		id,
		count(*) - 1 cnt
from		(
			select	mgrid id
			from	@t
			union all
			select	empid
			from	@t
		) q
group by	id

Peter Larsson
Helsingborg, Sweden

amalshah71
Starting Member

9 Posts

Posted - 2006-11-30 : 07:46:55

thnk u harsh.....tht helped and made me understand.....

cheers

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-30 : 07:53:55

I have done nothing more than editing Arvind's query!
It's those two (Arvind and Peso) who deserve the credit.

Thanks anyway.

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources