Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2008
    Posts
    1

    Unanswered: Help with select the most of each group

    I have a collection of records in a MS SQL table with the following fields:
    Computer, User, DateStamp

    Each time a user pushes a button on a computer, a record is inserted in the table showing the computer id, user id and the date it happened. Consequently, there are multiple records with a given computer id. A user could work on more than one computer. Therefore the data could look like:

    Machine1, User1, 1/3/2000
    Machine1, User1, 1/4/2000
    Machine1, User2, 1/5/2000
    Machine2, User4, 1/5/2000
    Machine3, User1, 1/9/2000
    Machine3, User1, 1/10/2000
    Machine3, User4, 1/11/2000
    Machine3, User3, 1/12/2000

    What I need is a list of the machines, the number of records for that machine, the name of the most common user of that machine, and the number of times that user showed up on that machine. The result table would look like:

    Machine1, 3, User1, 2
    Machine2, 1, User4, 1
    Machine3, 4, User1, 2

    I can create this as a stored procedure, but needed to make it into a single SELECT statement. Is it possible?

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    select b.computer, b.machine_count, c.User, c.user_count
    from
    (select computer, count(*) as machine_count
    from your_table
    group by computer) b,
    (select computer, User, count(*) as user_count
    from your_table a
    group by computer, User
    having count(*) >= ALL
    (select count(*)
    from your_table d
    where a.computer = d.computer
    group by computer, User
    )
    ) c
    where b.computer = c.computer

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by dhuskins
    I can create this as a stored procedure, but needed to make it into a single SELECT statement. Is it possible?
    I doubt your instructor would have assigned the homework if it was impossible.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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