select - Programmers Heaven

Howdy, Stranger!

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

Categories

Welcome to the new platform of Programmer's Heaven! We apologize for the inconvenience caused, if you visited us from a broken link of the previous version. The main reason to move to a new platform is to provide more effective and collaborative experience to you all. Please feel free to experience the new platform and use its exciting features. Contact us for any issue that you need to get clarified. We are more than happy to help you.

select

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

web_monitor_type
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?
Thanks

Sign In or Register to comment.