Hello my friends,

I am quite new to using SQL and need your expert help in solving a problem.

I have a table called Workstations that represents workstations installed in
various airports around the USA.

The workstation ID starts with the airport code (e.g. JFK), a terminal number, and an identifier.

For instance JFK1ABCD123 identifies a workstation in airport: JFK terminal 1 with an identifier: ABCD123.

The table named, Workstation was created with this DDL using SQL Server 2008 (for a SQL Server database named SampleDatabase). Please note that it could be ANY Database product out there. I am using SQL Server as an example only :

CREATE TABLE [dbo].[Workstation](
[WkstationID] [nvarchar](15) NOT NULL,
[Manufacturer] [nvarchar](50) NULL,
[OS] [nvarchar](50) NULL,
[IP] [nvarchar](15) NULL,
CONSTRAINT [PK_Workstation] PRIMARY KEY CLUSTERED
(
[WkstationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Here is the initial data:

USE SampleDatabase
GO

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('JFK1ABCD123', 'DELL', 'Windows 7.0', '123.123.123.21')

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('JFK1ABCD124', 'DELL', 'Windows XP', '123.123.123.22')

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('JFK1ABCD125', 'DELL', 'Windows NT', '123.123.123.23')

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('JFK4ABCD123', 'HP', 'Windows 7.0', '123.123.124.21')

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('JFK4ABCD124', 'HP', 'Windows XP', '123.123.124.22')

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('LAG3ABCD123', 'ACER', 'Windows NT', '123.123.125.21')

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('LAG3ABCD124', 'ACER', 'Windows 2003', '123.123.125.28')

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('LAG3ABCD125', 'ACER', 'Windows 7.0', '123.123.125.29')

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('REP2ABCD123', 'HP', 'Windows XP', '125.123.128.21')

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('REP2ABCD124', 'HP', 'Windows XP', '125.123.128.22')

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('REP2ABCD125', 'HP', 'Windows XP', '125.123.128.23')

INSERT INTO Workstation (WkstationID, Manufacturer, OS, IP)
VALUES ('REP2ABCD126', 'HP', 'Windows XP', '125.123.128.24')

GO

I need to produce a query result that counts the total number of workstation for each airport.
The output result should look like this based on the above data:

That is, based on the data inserted, 5 workstations are in airport JFK, 3 workstation in airport LAG and 4 workstations in airport REP

Does anyone know how to create a SQL query to produce the above output result?

Many thanks for your help in teaching this newbie.

you posted in the ANSI (=standard) SQL forum, but your SQL is definitely not ANSI compliant...

What have you got so far?

Check out SUBSTRING, GROUP BY and COUNT(*)!

Having more than one fact in a column is an extremely bad idea. Your WkstationID column has three, and is exactly why you're having a hard time writing your query.

A better design would be to hold a foreign key to a table of airport terminals (which itself would hold a foreign key to a table of airports) in your workstation table. Your query would then simply be:
You then have two options to maintain the workstation id, assuming there's a requirement that the value be formatted a certain way:

1. Set up a trigger to generate the value upon inserting or updating a row
2. Generate the value dynamically when selecting data. Something like: