select - Programmers Heaven

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!



oath73oath73 Posts: 1Member
I have two tables with the following data in them
switch_id iport oport
5 9 20
5 2 -1
5 12 13

switch_id type start_port end_port
5 DTMF 31 40
5 ISDN 1 3
5 ISDN 4 10
5 SS7 11 30

I need to read through each record in web_monitor, grab the correct type based on the ports and count each of those ports.

The output I'm trying to get is...
total_active_ports switch type
2 5 isdn
3 5 ss7

But, using the below statement, I'm getting these results...
total_active_ports switch type
2 5 isdn
2 5 ss7

SELECT count(web_monitor.switch_id) as switch_id_count,web_monitor.switch_id, type
from web_monitor,web_monitor_type where status <> 'Last Updated:'
and ((iport >= start_port and iport <= end_port)
or (oport >= start_port and oport <= end_port))
group by web_monitor.switch_id, type
order by web_monitor.switch_id, type

It wont allow me to count the same row multiple times to give me an accurate total.

Any idea how to get around this?

Sign In or Register to comment.