My count goes crazy - 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

SQL Server 2000 Forums

Transact-SQL (2000)

My count goes crazy

Author

Topic

zeezrom011
Starting Member

2 Posts

Posted - 2007-02-28 : 12:28:14

I have two queries that I want to join but when I do the column that holds a count goes nuts. I've tried joins and subqueries but I can't figure it out. Queries in question are:

select r.respemp, r.location, r.errtype, r.errdesc,case when r.respemp is null then 'UNKNOWN' ELSE r.empname end as EmpName, count(r.arpnum) as ErrCount
from RegistrationInfo r
where r.admitdate between '10/31/2006' and '12/31/2006'
group by r.respemp, r.location, r.errtype, r.errdesc, empname

select respemp, count(arpnum)
from RegsByEmp
where admitdate between '10/31/2006' and '12/31/2006'
group by respemp
order by respemp

Any and all help is appreciated.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-02-28 : 14:09:17

[code]SELECT p.respemp,
p.location,
p.errtype,
p.errdesc,
p.errcount
FROM (
select r.respemp,
r.location,
r.errtype,
r.errdesc,
case
when r.respemp is null then 'UNKNOWN'
ELSE r.empname
end as EmpName,
count(r.arpnum) as ErrCount
from RegistrationInfo as r
where r.admitdate between '10/31/2006' and '12/31/2006'
group by r.respemp,
r.location,
r.errtype,
r.errdesc,
case
when r.respemp is null then 'UNKNOWN'
ELSE r.empname
end
) AS p
INNER JOIN (
select respemp,
count(arpnum)
from RegsByEmp
where admitdate between '10/31/2006' and '12/31/2006'
group by respemp
) AS q ON q.respemp = p.respemp
ORDER BY p.respemp,
p.location[/code]

Peter Larsson
Helsingborg, Sweden

zeezrom011
Starting Member

2 Posts

Posted - 2007-02-28 : 14:38:05

Thank you Peter, that solution is excellent

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources