Results 1 to 3 of 3

Thread: Group By Count

  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: Group By Count

    Dear all

    How do i group the count by Top ?

    For example, We want to show Top 5 high salary people in my company , but may top 3 people's salary is same. So I want to sperate 5 groups, the output data look like this :
    [Top 1 high salary] - Philip , Alex , David
    [Top 2 high salary] - Emily
    [Top 3 high salary] - Tom, May, Ada
    [Top 4 high salary] - Queenie
    [Top 5 high salary] - Ellie, Candy, Hazel, Stella, Tanya, Jacky

    Hence, the total count of name should be over 5, so how do i output this record ? because it should just show 5 peoples (Philip, Alex, David, Emily and Tom) if i just use "Top 5" .

    Thanks you for your help

  2. #2
    Join Date
    May 2003
    Location
    Parsippany NJ
    Posts
    36
    in SQL server, you could declare table variable, and insert those names into the table variable, then return from the table variable

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    This is effectively a pivot on top of aggregation. That means that it is best done on the client.

    With that said, it can be done in SQL. Something like:
    PHP Code:
    CREATE TABLE dbo.salary (
       
    name        VARCHAR(20)    NOT NULL
    ,  salary    MONEY        NOT NULL
       
    )

    INSERT dbo.salary (salary, [name]) VALUES (50'Phillip')
    INSERT dbo.salary (salary, [name]) VALUES (50'Alex')
    INSERT dbo.salary (salary, [name]) VALUES (50'David')
    INSERT dbo.salary (salary, [name]) VALUES (40'Emily')
    INSERT dbo.salary (salary, [name]) VALUES (30'Tom')
    INSERT dbo.salary (salary, [name]) VALUES (30'May')
    INSERT dbo.salary (salary, [name]) VALUES (30'Ada')
    INSERT dbo.salary (salary, [name]) VALUES (20'Queenie')
    INSERT dbo.salary (salary, [name]) VALUES (10'Ellie')
    INSERT dbo.salary (salary, [name]) VALUES (10'Candy')
    INSERT dbo.salary (salary, [name]) VALUES (10'Hazel')
    INSERT dbo.salary (salary, [name]) VALUES (10'Stella')
    INSERT dbo.salary (salary, [name]) VALUES (10'Tanya')
    INSERT dbo.salary (salary, [name]) VALUES (10'Jacky')

    INSERT dbo.salary (salary, [name]) VALUES 5'Sam')
    INSERT dbo.salary (salary, [name]) VALUES 5'Sammy')

    INSERT dbo.salary (salary, [name]) VALUES 3'Clyde')

    SELECT TOP 5 r.salaryMin(s1.[name]) + Coalesce', ' Min(s2.[name]), ''
    +  
    Coalesce', ' Min(s3.[name]), '') + Coalesce', ' Min(s4.[name]), '')
    +  
    Coalesce', ' Min(s5.[name]), '')
       
    FROM (SELECT TOP 5 salary FROM dbo.salary GROUP BY salary ORDER BY salary DESC) AS r
       JOIN 
    (SELECT salary, [nameFROM dbo.salary) AS s1
          ON 
    (s1.salary r.salary)
       
    LEFT JOIN (SELECT salary, [nameFROM dbo.salary) AS s2
          ON 
    (s2.salary r.salary
          
    AND s2.[name] > s1.[name])
       
    LEFT JOIN (SELECT salary, [nameFROM dbo.salary) AS s3
          ON 
    (s3.salary r.salary
          
    AND s3.[name] > s2.[name])
       
    LEFT JOIN (SELECT salary, [nameFROM dbo.salary) AS s4
          ON 
    (s4.salary r.salary
          
    AND s4.[name] > s3.[name])
       
    LEFT JOIN (SELECT salary, [nameFROM dbo.salary) AS s5
          ON 
    (s5.salary r.salary
          
    AND s5.[name] > s4.[name])
       
    GROUP BY r.salary
       ORDER BY r
    .salary DESC 
    -PatP

Posting Permissions

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