Professional Web Applications Themes

select with count, group and where condition - MySQL

Can someone clarify the syntax of a select statement I'm having trouble writing? I have this query:   from users group by client_id; +-----------+---+ | client_id | c | +-----------+---+ | 0 | 2 | | 1 | 1 | | 2 | 1 | | 10 | 1 | +-----------+---+ 4 rows in set (0.01 sec) which is the correct result set. However, what I actually need are the rows where c > 1 (just the top row in the example above). What's the syntax for that? My reading of the manual suggests this incorrect answer:   from users ...

  1. #1

    Default select with count, group and where condition

    Can someone clarify the syntax of a select statement I'm having trouble
    writing? I have this query:
     
    from users group by client_id;
    +-----------+---+
    | client_id | c |
    +-----------+---+
    | 0 | 2 |
    | 1 | 1 |
    | 2 | 1 |
    | 10 | 1 |
    +-----------+---+
    4 rows in set (0.01 sec)

    which is the correct result set. However, what I actually need are the
    rows where c > 1 (just the top row in the example above). What's the
    syntax for that? My reading of the manual suggests this incorrect answer:
     
    from users where c > 1 group by client_id;
    ERROR 1054 (42S22): Unknown column 'c' in 'where clause'

    I've tried lots of other variations but can't work it out.

    --
    Derek Fountain on the web at http://www.derekfountain.org/
    Derek Guest

  2. #2

    Default Re: select with count, group and where condition

    Derek Fountain wrote: 
    > from users group by client_id;
    > +-----------+---+
    > | client_id | c |
    > +-----------+---+
    > | 0 | 2 |
    > | 1 | 1 |
    > | 2 | 1 |
    > | 10 | 1 |
    > +-----------+---+
    > 4 rows in set (0.01 sec)
    >
    > which is the correct result set. However, what I actually need are the
    > rows where c > 1 (just the top row in the example above). What's the
    > syntax for that? My reading of the manual suggests this incorrect answer:

    > from users where c > 1 group by client_id;
    > ERROR 1054 (42S22): Unknown column 'c' in 'where clause'
    >
    > I've tried lots of other variations but can't work it out.
    >[/ref]

    Since c is a calculated column, it's use in the WHERE clause is
    meaningless. In your case, for instance, if you got the syntax correct,
    you would get no output because each row examined would have a count of
    1 - and therefore be discarded (setting the count back to 0).

    Try

    SELECT client_id, COUNT(client_id) AS c
    FROM USERS
    GROUP BY client_id
    HAVING c > 1;

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

  3. #3

    Default Re: select with count, group and where condition

    > Try 

    Works a treat, thanks. :)

    --
    Derek Fountain on the web at http://www.derekfountain.org/
    Derek Guest

Similar Threads

  1. Select, count(), group by and order by
    By Beauregard in forum MySQL
    Replies: 8
    Last Post: January 18th, 06:03 PM
  2. select a column with a condition
    By gehegeradeaus@gmail.com in forum MySQL
    Replies: 4
    Last Post: December 6th, 11:57 AM
  3. Replies: 8
    Last Post: March 19th, 07:16 AM
  4. Replies: 4
    Last Post: February 16th, 11:59 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