Professional Web Applications Themes

Order by results of count using 'group by' - MySQL

Hi there, I'm querying a single table with the goal of selecting the number of distinct cities of the field 'city' where the count is greater than 9 and ordering by the result by the count. The closest I've been able to come is this: 'select city,count(1) as list from residential group by city order by list desc;' Which will order the full list. Inserting a where statement "where list > 10" seems to keep resulting in a syntax error. Is it possible to limit my results in the sql statement, or should I do it in my programming logic ...

  1. #1

    Default Order by results of count using 'group by'

    Hi there,

    I'm querying a single table with the goal of selecting the number of
    distinct cities of the field 'city' where the count is greater than 9
    and ordering by the result by the count. The closest I've been able to
    come is this:

    'select city,count(1) as list from residential group by city order by
    list desc;'

    Which will order the full list. Inserting a where statement "where
    list > 10" seems to keep resulting in a syntax error. Is it possible
    to limit my results in the sql statement, or should I do it in my
    programming logic instead?

    salvador Guest

  2. #2

    Default Re: Order by results of count using 'group by'

    salvador wrote:
    > Hi there,
    >
    > I'm querying a single table with the goal of selecting the number of
    > distinct cities of the field 'city' where the count is greater than 9
    > and ordering by the result by the count. The closest I've been able to
    > come is this:
    >
    > 'select city,count(1) as list from residential group by city order by
    > list desc;'
    >
    > Which will order the full list. Inserting a where statement "where
    > list > 10" seems to keep resulting in a syntax error. Is it possible
    > to limit my results in the sql statement, or should I do it in my
    > programming logic instead?
    >
    If you want to restrict the search on a condition involving the result of an aggregate function you must use the HAVING
    clause instead of the WHERE clause.

    select
    city, count(*) as list
    from
    residential
    group by
    city
    having
    list > 10
    order by
    list desc;

    ciao
    gmax

    --
    _ _ _ _
    (_|| | |(_|>< The Data Charmer
    _|
    [url]http://datacharmer.org/[/url]
    Giuseppe Maxia Guest

  3. #3

    Default Re: Order by results of count using 'group by'

    salvador wrote:
    > Hi there,
    >
    > I'm querying a single table with the goal of selecting the number of
    > distinct cities of the field 'city' where the count is greater than 9
    > and ordering by the result by the count. The closest I've been able to
    > come is this:
    >
    > 'select city,count(1) as list from residential group by city order by
    > list desc;'
    >
    > Which will order the full list. Inserting a where statement "where
    > list > 10" seems to keep resulting in a syntax error. Is it possible
    > to limit my results in the sql statement, or should I do it in my
    > programming logic instead?
    >
    Something like:

    SELECT city, count( * ) AS cnt
    FROM residential
    GROUP BY city
    having cnt > 9
    order by cnt desc

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    [email]jstucklexattglobal.net[/email]
    ==================
    Jerry Stuckle Guest

  4. #4

    Default Re: Order by results of count using 'group by'

    Hey Jerry and Giuseppe,

    Thanks a bunch for your help. This worked great!.

    -Sal

    Giuseppe Maxia wrote:
    > salvador wrote:
    > > Hi there,
    > >
    > > I'm querying a single table with the goal of selecting the number of
    > > distinct cities of the field 'city' where the count is greater than 9
    > > and ordering by the result by the count. The closest I've been able to
    > > come is this:
    > >
    > > 'select city,count(1) as list from residential group by city order by
    > > list desc;'
    > >
    > > Which will order the full list. Inserting a where statement "where
    > > list > 10" seems to keep resulting in a syntax error. Is it possible
    > > to limit my results in the sql statement, or should I do it in my
    > > programming logic instead?
    > >
    >
    > If you want to restrict the search on a condition involving the result of an aggregate function you must use the HAVING
    > clause instead of the WHERE clause.
    >
    > select
    > city, count(*) as list
    > from
    > residential
    > group by
    > city
    > having
    > list > 10
    > order by
    > list desc;
    >
    > ciao
    > gmax
    >
    > --
    > _ _ _ _
    > (_|| | |(_|>< The Data Charmer
    > _|
    > [url]http://datacharmer.org/[/url]
    salvador Guest

Similar Threads

  1. Count GROUP BY query dilemma
    By Bosconian in forum MySQL
    Replies: 5
    Last Post: December 23rd, 07:03 AM
  2. COUNT and GROUP
    By brianism in forum Coldfusion Database Access
    Replies: 2
    Last Post: December 2nd, 09:09 PM
  3. ASP Group Same Records and Give Total Count
    By gotcha in forum ASP Database
    Replies: 1
    Last Post: August 6th, 08:50 PM
  4. Need Asp recordset Group / Count like values
    By gotcha in forum ASP Database
    Replies: 2
    Last Post: July 20th, 05:12 PM
  5. Group by, count, type of query beyond my ability
    By Ray at in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 8th, 09:29 AM

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