SQL Select

Hello,

The query below works great except when count(*) as processes is = 0 in which case it will always select 1.  How can I fix this?  When the count(*) is 1 it selects 1 as it should.

Thanks
select s.serverip, s.serverid, count(*) as processes, s.capacity 
	from servers s left outer join processqueue p on s.serverid = p.serverid 
	where(s.status = 100) 
	group by s.serverid, p.serverid, s.capacity, s.serverip 
	having count(*) < s.capacity 
	order by count(*)

Open in new window

grogo21Asked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
Something like this perhaps:

select      s.serverip,
            s.serverid,
            s.capacity,
            p.processes
from      servers s
            left join (
                        Select      serverid,
                                    count(*) processes
                        From      processqueue
                        Group By
                                    serverid) p on s.serverid = p.serverid
where      s.status = 100
            And p.processes < s.capacity
order by
            p.processes
0
 
dan_nealConnect With a Mentor Commented:
Or just as simple:
select s.serverip, s.serverid, count(*) as processes, s.capacity 
	from servers s left outer join processqueue p on s.serverid = p.serverid 
	where(s.status = 100) 
	group by s.serverid, p.serverid, s.capacity, s.serverip 
	having count(*) < s.capacity and count(*) > 0
	order by count(*)

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.