Home » SQL & PL/SQL » SQL & PL/SQL » SQL for counting items in a list, from cropped fields (Oracle, 10.2.0.2, Solaris10_4u)
SQL for counting items in a list, from cropped fields [message #355936] Tue, 28 October 2008 11:24 Go to next message
lbjerke
Messages: 2
Registered: October 2008
Junior Member

Hello all,

GOAL: Find how many computers are on the new domain and find out how many computers are still on the old domain.

PRE: I have ran a script to collect the 'DNS Search Domain' from the computers on my networks, and output the results to the Oracle Database. I have a Table with columns: CompID, Domain_Name, Domain_Order

SELECT DNS.DOMAIN_NAME, COUNT(DNS.DOMAIN_NAME) AS "COUNT"
FROM DEVICE_DNS_SEARCH_DOMAINS DNS
WHERE DNS.DOMAIN_ORDER LIKE '0'
GROUP BY DNS.DOMAIN_NAME


results:
DOMAIN_NAME       COUNT
---------------   -----
SUB1.NEWLAB.NET      18
SUB2.NEWLAB.NET       2
SUB1.OLDLAB.NET       3
SUB2.OLDLAB.NET       2


Real Goal:
I am trying to create a list that would grab all DOMAINS, regardless of SUB-DOMAIN and give an accurate output count, without hard coding the domain names in the SQL. I have been Googling like a crazy man and trying various things, but have yet reached my GOAL of a query that will show me how many servers are on the right/wrong domains.

My GOAL is to get the following results:
DOMAIN_NAME   COUNT
-----------   -----
NETLAB.NET      20
OLDLAB.NET       5



Any help or references of things to look up or try would be greatly appreciated. Thank you!

--lars
Re: SQL for counting items in a list, from cropped fields [message #355937 is a reply to message #355936] Tue, 28 October 2008 11:33 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
I am really glad that somebody has followed the forum guidelines in their very first post. Hence I am not following the guidelines Smile

Check this link for more information.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions068.htm#SQLRF00651

with t
as
(select 'SUB1.NEWLAB.NET' domain_name from dual union all
 select 'SUB1.NEWLAB.NET' from dual union all
 select 'SUB1.NEWLAB.NET' from dual union all
 select 'SUB2.NEWLAB.NET' from dual union all
 select 'SUB1.OLDLAB.NET' from dual union all
 select 'SUB1.OLDLAB.NET' from dual union all
 select 'SUB2.OLDLAB.NET' from dual
)
select substr(domain_name, instr(domain_name,'.',-1,2) + 1) , count(*) from t
group by substr(domain_name, instr(domain_name,'.',-1,2) + 1);


My assumption is you always want to group on the last two strings of your domain.

Hope this helps.

Regards

Raj
Re: SQL for counting items in a list, from cropped fields [message #355953 is a reply to message #355937] Tue, 28 October 2008 12:51 Go to previous message
lbjerke
Messages: 2
Registered: October 2008
Junior Member

raj,

That was just what I was looking for!

I didn't want to hard code any domain names to ensure I got a full list, in case any of the servers were on the wrong domains (or typo). I was able to shorten the first section to allow this.

Thanks for all your help Raj!

Final Code:
with t
as
(
SELECT LDNS.DOMAIN_NAME
         FROM LAB.DEVICE_DNS_SEARCH_DOMAINS LDNS
         WHERE   LDNS.DOMAIN_ORDER LIKE '0'
)
select substr(domain_name, instr(domain_name,'.',-1,2) + 1) as "DOMAIN", count(*) as "Server Count" from t
group by substr(domain_name, instr(domain_name,'.',-1,2) + 1);


Output:
DOMAIN        Server Count
-----------   ------------
NEWLAB.NET       20
OLDLAB.NET        5



Thanks again,

--lars
Previous Topic: Record frequency of App Module used
Next Topic: oracle view
Goto Forum:
  


Current Time: Wed Dec 07 18:56:32 CST 2016

Total time taken to generate the page: 4.16716 seconds