I have a table consisting of two fields, OStype and OSversion, with
entries like:

OStype OSversion
solaris 2.5
solaris 2.6
redhat 6.2
redhat 6.2
solaris 8
redhat AS4
solaris 10
solaris 10
redhat AS2.1
redhat AS3
redhat AS4

I want to create a select statement that returns for each OS type, the
total number of entries and for each version the total number of
entries.

In the example the result would be:

OStype OStype Count OSversion OSversion Count
solaris 5
2.5 1
2.6 1
8 1
10 2
redhat 6
6.2 2
AS2.1 1
AS3 1
AS4 2


Thanks in advance for your help.

Ian

ian.l.guthrie@gmail.com wrote:
> I have a table consisting of two fields, OStype and OSversion, with
> entries like:
>
> OStype OSversion
> solaris 2.5
> solaris 2.6
> redhat 6.2
> redhat 6.2
> solaris 8
> redhat AS4
> solaris 10
> solaris 10
> redhat AS2.1
> redhat AS3
> redhat AS4
>
> I want to create a select statement that returns for each OS type, the
> total number of entries and for each version the total number of
> entries.
>
> In the example the result would be:
>
> OStype OStype Count OSversion OSversion Count
> solaris 5
> 2.5 1
> 2.6 1
> 8 1
> 10 2
> redhat 6
> 6.2 2
> AS2.1 1
> AS3 1
> AS4 2
>
>
> Thanks in advance for your help.
>
> Ian

Try CUBE/ROLLUP:

SELECT ostype, osversion, COUNT(*) AS cnt
FROM tbl
GROUP BY ostype, osversion
WITH ROLLUP ;

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--

Also if you want to suppress the result, use front end application

Madhivanan


ian.l.guthrie@gmail.com wrote:
> I have a table consisting of two fields, OStype and OSversion, with
> entries like:
>
> OStype OSversion
> solaris 2.5
> solaris 2.6
> redhat 6.2
> redhat 6.2
> solaris 8
> redhat AS4
> solaris 10
> solaris 10
> redhat AS2.1
> redhat AS3
> redhat AS4
>
> I want to create a select statement that returns for each OS type, the
> total number of entries and for each version the total number of
> entries.
>
> In the example the result would be:
>
> OStype OStype Count OSversion OSversion Count
> solaris 5
> 2.5 1
> 2.6 1
> 8 1
> 10 2
> redhat 6
> 6.2 2
> AS2.1 1
> AS3 1
> AS4 2
>
>
> Thanks in advance for your help.
>
> Ian

Thanks David (and all)...that worked!

-Ian
David Portas wrote:
> ian.l.guthrie@gmail.com wrote:
> > I have a table consisting of two fields, OStype and OSversion, with
> > entries like:
> >
> > OStype OSversion
> > solaris 2.5
> > solaris 2.6
> > redhat 6.2
> > redhat 6.2
> > solaris 8
> > redhat AS4
> > solaris 10
> > solaris 10
> > redhat AS2.1
> > redhat AS3
> > redhat AS4
> >
> > I want to create a select statement that returns for each OS type, the
> > total number of entries and for each version the total number of
> > entries.
> >
> > In the example the result would be:
> >
> > OStype OStype Count OSversion OSversion Count
> > solaris 5
> > 2.5 1
> > 2.6 1
> > 8 1
> > 10 2
> > redhat 6
> > 6.2 2
> > AS2.1 1
> > AS3 1
> > AS4 2
> >
> >
> > Thanks in advance for your help.
> >
> > Ian
>
> Try CUBE/ROLLUP:
>
> SELECT ostype, osversion, COUNT(*) AS cnt
> FROM tbl
> GROUP BY ostype, osversion
> WITH ROLLUP ;
>
> --
> David Portas, SQL Server MVP
>
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
>
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
> --