Unanswered: SQL Query Question: How to count number of occurences of a Key Column
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 :
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:
group by airport_code
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:
airport_code || terminal_nr || workstation_nr as workstation_id
Last edited by futurity; 06-08-11 at 13:40.
Reason: Read the OP more closely