Results 1 to 3 of 3

Thread: Group By Count

  1. #1
    Join Date
    Mar 2004

    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
    Parsippany NJ
    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
    In front of the computer
    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
    (SELECT salary, [nameFROM dbo.salary) AS s1
    (s1.salary r.salary)
    LEFT JOIN (SELECT salary, [nameFROM dbo.salary) AS s2
    (s2.salary r.salary
    AND s2.[name] > s1.[name])
    LEFT JOIN (SELECT salary, [nameFROM dbo.salary) AS s3
    (s3.salary r.salary
    AND s3.[name] > s2.[name])
    LEFT JOIN (SELECT salary, [nameFROM dbo.salary) AS s4
    (s4.salary r.salary
    AND s4.[name] > s3.[name])
    LEFT JOIN (SELECT salary, [nameFROM dbo.salary) AS s5
    (s5.salary r.salary
    AND s5.[name] > s4.[name])
    GROUP BY r.salary
       ORDER BY r
    .salary DESC 

Posting Permissions

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