Professional Web Applications Themes

how to find out max group count - Microsoft SQL / MS SQL Server

Would it be possible to find out which person has sold the most items? table: item colums: item_id, item_desc, saleperson_id (null means item has not been sold, not null means has been sold by the person with the id) This query made sense to me that it should work but I got this error "Cannot perform an aggregate function on an expression containing an aggregate or a subquery" select max(count(*)) from item where saleperson_id is not null group by saleperson_id Thanks in advance for your help...

  1. #1

    Default how to find out max group count


    Would it be possible to find out which person has sold the most items?

    table: item
    colums: item_id, item_desc, saleperson_id (null means item has not been
    sold, not null means has been sold by the person with the id)

    This query made sense to me that it should work but I got this error "Cannot
    perform an aggregate function on an expression containing an aggregate or a
    subquery"

    select max(count(*))
    from item
    where saleperson_id is not null
    group by saleperson_id

    Thanks in advance for your help


    Zeng Guest

  2. #2

    Default Re: how to find out max group count

    SELECT saleperson_id
    FROM item
    WHERE saleperson_id IS NOT NULL
    GROUP BY saleperson_id
    HAVING COUNT(*) >= ALL
    (SELECT COUNT(*)
    FROM item
    WHERE saleperson_id IS NOT NULL
    GROUP BY saleperson_id)

    Remember that there could be a tie for the top selling saleperson in which
    case you will get more than one row.

    --
    David Portas
    ------------
    Please reply only to the newsgroup
    --



    David Guest

  3. #3

    Default Re: how to find out max group count

    That works well, thanks. Although, I still don't know what the "X" in the
    "X(tally)" is for.


    "Joe Celko" <com> wrote in message
    news:phx.gbl... 


    Zeng Guest

Similar Threads

  1. Order by results of count using 'group by'
    By salvador in forum MySQL
    Replies: 3
    Last Post: August 2nd, 06:49 PM
  2. Count GROUP BY query dilemma
    By Bosconian in forum MySQL
    Replies: 5
    Last Post: December 23rd, 07:03 AM
  3. COUNT and GROUP
    By brianism in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 2nd, 09:09 PM
  4. ASP Group Same Records and Give Total Count
    By gotcha in forum ASP Database
    Replies: 1
    Last Post: August 6th, 08:50 PM
  5. Need Asp recordset Group / Count like values
    By gotcha in forum ASP Database
    Replies: 2
    Last Post: July 20th, 05:12 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139