Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    1

    Question 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 :

    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:
    HTML Code:
    AIRPORT   TOTAL_WORKSTATIONS
    --------   -----------------------
    JFK              5
    LAG              3
    REP              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.

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    you posted in the ANSI (=standard) SQL forum, but your SQL is definitely not ANSI compliant...

  3. #3
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    What have you got so far?

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

  4. #4
    Join Date
    May 2008
    Posts
    277
    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:
    Code:
    select
        airport_code,
        count(*)
    from workstation
    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:
    Code:
    select
        airport_code || terminal_nr || workstation_nr as workstation_id
        ...
    from workstation
    Last edited by futurity; 06-08-11 at 13:40. Reason: Read the OP more closely

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •