Need to collect server Name and Server DNS Name as Same 1

[ljsmith91]

I am looking to grab some totals from an Oracle SQL database but my problem is that the Server name is listed in the database under both its name and its dns name. I want to total the GB Backed up whether it was recorded under its simple name or DNS name.

Simplified sample of the server table:

server Backed Up

server01.myplace.com 20
server01 14
server32 12
server32 10
server99 30
server231 30
server231.yourplace.com 20


If I want to query this server table and treat the server01 and server01.myplace.com as the same servers(they are) and the server231 and server231.yourplace.com as the same servers(they are) so that the totaled results would result in the list below, how would I ? I hope I can but lack the knowledge of how to do so. Thanks for any direction or help.

Note...I want to lose the DNS server name in the query results and total all the Backed up numbers.

Query result:

server Total Backed Up

server01 34
server32 22
server99 30
server231 50

 

[LKBrwnDBA]

Try something like:

Select SUBSTR(server||'.',1,INSTR(server,'.')-1) "Server"
     , SUM(backed_up) "Total Backed Up"
  From ServerTable
 Group By SUBSTR(server||'.',1,INSTR(server,'.')-1);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ljsmith91]

LKBrwnDBA,

Thanks to your code example I do believe that it should do exactly what I need. I was not familiar with the INSTR function until now...looks great. Will apply your logic to my real code. Thanks so much for the great help.

ljs

 

[ljsmith91]

LKBrwnDBA,

That only grabbed those servers that had a DNS entry and ignored those servers without it.... I need to grab all servers whether they have a DNS entry or not and total them. And as shown in my example, somethimes they have both a DNS and there regular name and sometimes they have just there regular name.

Any ideas ?

ljs

 

[LKBrwnDBA]

Oooops sorry dot was in the wrong place:

Select SUBSTR(server,1,INSTR(server||'.','.')-1) "Server"
     , SUM(backed_up) "Total Backed Up"
  From ServerTable
 Group By SUBSTR(server||'.',1,INSTR(server,'.')-1);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[LKBrwnDBA]

PS: Forgot to change the Group By also:

Select SUBSTR(server,1,INSTR(server||'.','.')-1) "Server"
     , SUM(backed_up) "Total Backed Up"
  From ServerTable
 Group By SUBSTR(server,1,INSTR(server||'.','.')-1);

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ljsmith91]

LKBrwnDBA,

That did it.... thanks so much.

ljs