473,322 Members | 1,259 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

SQL select statement needed

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

Aug 16 '06 #1
3 1515
ia***********@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
--

Aug 16 '06 #2

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

Madhivanan
ia***********@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
Aug 17 '06 #3
Thanks David (and all)...that worked!

-Ian
David Portas wrote:
ia***********@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
--
Aug 17 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
by: Guy Hocking | last post by:
Hi there, I have a problem in my ASP/SQL Server application i am developing, i hope you guys can help. I have a ASP form with list boxes populated by SQL tables. When a user selects a value...
11
by: Jeff Sandler | last post by:
I need a MySQL select statement as part of a PHP script. I want to find rows where a certain column either starts with or equals a user-supplied string. The string will be 1 or more characters in...
5
by: Muhd | last post by:
Hey all, I have a basic table that looks something like this. CREATE TABLE MyTable ( ID INT IDENTITY PRIMARY KEY, Company_ID INT NOT NULL, Round VARCHAR(50) NOT NULL, Details VARCHAR(250)...
4
by: Terencetrent | last post by:
I having been using Access '97/2002 for about 4 years now and have never really had the need or the time to learn visual basic. Well, I think the time has finally come. I need help with Visual...
4
by: Polly | last post by:
I had a macro that ran a parameter query and created and opened an Excel file with the system date as part of the file name, but I had to change the file name by hand. So I converted the macro to...
6
by: GSteven | last post by:
(as formerly posted to microsoft.public.access.forms with no result) I've created a continuous form which is based on a straightforward table (ex - customers - 100 records). On the form there is...
7
by: Matt Jensen | last post by:
Howdy Fairly simple question I think, I presume the answer is no it can't be reused for 2 *SELECT* statements, but just hoping for clarification. Just asking in the interests of trying to minimise...
4
by: Nick Barr | last post by:
Hi, I am trying to gather stats about how many times a resource in our web app is viewed, i.e. just a COUNT. There are potentially millions of resources within the system. I thought of two...
4
by: Matt Ratliff | last post by:
Hello, I would appreciate any assistance you have with the following problem: I have (as an example) an array of values as follows: arrayvalues=new Array("0001","0003","0005") where each is the...
3
by: terminon | last post by:
New to the forum, and a relatively new with SQL, but until now I've been able to get everything to work just fine. I've spent hours trying to get this select statement to work right, and I've never...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.