SQL Query Question: How to count number of items for each key column

Question

Post reply

SQL Query Question: How to count number of items for each key column

limnath 78320

Grasshopper

Points: 11
More actions
June 5, 2011 at 8:15 pm

#389658

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) :
CREATE TABLE [dbo].[Workstation](
[WkstationID] [nvarchar](15) NOT NULL,
[Manufacturer] [nvarchar](50) NULL,
[OS] [nvarchar](50) NULL,
127.0.0.1 [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:
AIRPORT &nbsp TOTAL_WORKSTATIONS
-------- &nbsp -----------------------
JFK &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 5
LAG &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 3
REP &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp &nbsp 4
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.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63503 More actions · Answer 1

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')

For starters, you have a slightly flawed design. Creating "intelligent" keys is usually a disaster, and very hard to maintain. Put the Airport Code into another field and then the query becomes stupid easy.

CREATE TABLE [dbo].[Workstation](

[WkstationID] [nvarchar](15) NOT NULL,

[AIRPORT_ID] CHAR(3) NOT NULL, -- NOTE THE NEW FIELD....

[Manufacturer] [nvarchar](50) NULL,

[OS] [nvarchar](50) NULL,

127.0.0.1 [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]

Then your answer is just

SELECT Airport_ID, COUNT(WorkstationID)

FROM WorkStation

GROUP BY Airport_ID

ORDER BY Airport_ID;

Latheesh NK SSCertifiable Points: 6559 More actions · Answer 2

As pietlindan said, you need to revisit your design.

However, check the following would work or not....Again, it would better if you could revisit the design .

Select substring(WkstationID,0,4),COUNT(1) From Workstation

Group by substring(WkstationID,0,4)

Order by substring(WkstationID,0,4)

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 3

Try this:

SELECT LEFT( WkstationID, 3 ) WkstationID , COUNT(*) COUNTS

FROM Workstation

GROUP BY LEFT( WkstationID, 3 )

limnath 78320 Grasshopper Points: 11 More actions · Answer 4

Thank you very much for your kind reply and critique. I will take your advise into consideration.

limnath 78320 Grasshopper Points: 11 More actions · Answer 5

Thanks for your advise and your help.

The following response from ColdCoffee works too:

SELECT LEFT( WkstationID, 3 ) WkstationID , COUNT(*) COUNTS

FROM Workstation

GROUP BY LEFT( WkstationID, 3)