SQL tables creation problems - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL tables creation problems

Author

Topic

junes
Starting Member

12 Posts

Posted - 2008-10-23 : 05:51:29

All,
Please can you help.
I am new to SQL and i want to create a table as shown below,

Servers by OS Total
AIX 50
Solaris 20
Linux 30
Windows 10
OpenVMS 7
VmWare 6
Total 123

Here is my SQL code and i only getting the following ouput.

select count(hs.hostname) as HOSTS
from
mvc_hostsummaryvw hs
where
hs.os='AIX'
UNION all
select count(hs.hostname) as HOSTS
from
mvc_hostsummaryvw hs
where
hs.os='Solaris'
UNION all
select count(hs.hostname) as HOSTS
from
mvc_hostsummaryvw hs
where
(hs.os='Windows(R) Server 2000' OR hs.os='Windows(R) Server 2003' OR hs.os='Windows(R) Server 2008')

Output of the SQL script
-----------------------
Hosts
58
34
36

How can i create two columns (Servers by OS & Total) and a row at the end which displays a grand total.

Any help will be greatly appreciated.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 06:02:39

can you give some sample data too as we dont know what all field your table has and which of them contain required data.

junes
Starting Member

12 Posts

Posted - 2008-10-23 : 06:16:55

Is this what you are looking for:

Column Name Data Type Type_Name
HOSTID 3 NUMBER
HOSTNAME 12 VARCHAR2
DOMAINID 3 NUMBER
VENDOR 12 VARCHAR2
DESCRIPTION 12 VARCHAR2
STATUS 3 NUMBER
IP 12 VARCHAR2
DNS 12 VARCHAR2
MODEL 12 VARCHAR2
VERSION 12 VARCHAR2
OS 12 VARCHAR2
MODEL_TYPE 3 NUMBER
ISCLUSTER 3 NUMBER
TOTALPHYSICALMEM 3 NUMBER
NUMBERPROCESSOR 3 NUMBER
SUPPORTFLAG 3 NUMBER
BASETABLENAME 1 CHAR

Output of the table SAMPLE 1
----------------------------

122162 EMALONFNP01 1000 HP AT/AT COMPATIBLE 4 165.47.61.195 EMALONFNP01.emea.bankofamerica.com ProLiant DL380 G4 5.2.3790 Windows(R) Server 2003 1 (null) 3669476 4 7 HOST
151085 lonecnsddb01 1000 SUN FJSV,GPUZC-M 4 165.47.191.78 lonecnsddb01.emea.bankofamerica.com sun4us Generic_118833-36.5.10 Solaris 1 (null) 8388608 2 7 HOST

Output of the table SAMPLE 2
----------------------------

151085 lonecnsddb01 1000 SUN FJSV,GPUZC-M 4 165.47.191.78 lonecnsddb01.emea.bankofamerica.com sun4us Generic_118833-36.5.10 Solaris 1 (null) 8388608 2 7 HOST

cvraghu
Posting Yak Master

187 Posts

Posted - 2008-10-23 : 06:18:30

select hs.os, count(*)
from
mvc_hostsummaryvw hs
Group by hs.os

Union all

Select 'Total', count(*)
from mvc_hostsummaryvw

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 06:19:55

seems like what you want is

SELECT os,count(*) as Total
FROM mvc_hostsummaryvw 
GROUP BY os

junes
Starting Member

12 Posts

Posted - 2008-10-23 : 07:11:14

Many thanks for this, I can't believe the syntax was so simple, compare to what i was writing.

I changed the code slighlty as follows:

select hs.os, count(*) as OS_NUMBER
from mvc_hostsummaryvw hs
where hs.os is not null
GROUP BY hs.os
Union all
Select 'Total', count(*)
from mvc_hostsummaryvw hss
where hss.os is not null;

I have two more question please.

How can i sort the ouput to have AIX on top of the list, so the data in ASC order. I have attempted to change the code but with no luck. Also how can i change the title from "OS" to "SAN Connected Servers by OS". Thanks alot.

OS OS_NUMBER
---------------
HP-UX 1
Windows(R) Server 2003 36
Solaris 34
AIX 58
Total 129

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 07:13:14

[code]select os,OS_NUMBER
FROM
(
select hs.os, count(*) as OS_NUMBER,0 AS Cat
from mvc_hostsummaryvw hs
where hs.os is not null
GROUP BY hs.os
Union all
Select 'Total', count(*),1
from mvc_hostsummaryvw hss
where hss.os is not null;
)t
ORDER BY Cat,os[/code]

junes
Starting Member

12 Posts

Posted - 2008-10-23 : 07:34:41

Visakh16,

How can i change the Coulmn name from "OS" to "SAN Connected Servers by OS".

OS OS_NUMBER
---------------

SAN Connected Servers by OS OS_NUMBER
----------------------------------------

Thanks Alot.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-23 : 07:45:39

quote:
Originally posted by junes

Visakh16,

How can i change the Coulmn name from "OS" to "SAN Connected Servers by OS".

OS OS_NUMBER
---------------

SAN Connected Servers by OS OS_NUMBER
----------------------------------------

Thanks Alot.

use AS

select os AS [SAN Connected Servers by OS],
OS_NUMBER 
FROM
(
select hs.os, count(*) as OS_NUMBER,0 AS Cat
from mvc_hostsummaryvw hs
where hs.os is not null
GROUP BY hs.os
Union all
Select 'Total', count(*),1
from mvc_hostsummaryvw hss
where hss.os is not null;
)t
ORDER BY Cat,os

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources