Professional Web Applications Themes

distinct, count - MySQL

hello, is there something wrong w/this query? SELECT DISTINCT(tbuser.fk_userid), COUNT(tbuser.fk_userid) AS cuserid........ I'm getting this error: Unknown table 'tbuser' in field list (table is there, use in other queries..) I'm converting from a DB2 VIEW here... orig was: select count(distinct(tbuser.fk_userid)) thank you very much.. Frances...

  1. #1

    Default distinct, count

    hello, is there something wrong w/this query?

    SELECT DISTINCT(tbuser.fk_userid), COUNT(tbuser.fk_userid) AS
    cuserid........

    I'm getting this error: Unknown table 'tbuser' in field list
    (table is there, use in other queries..)

    I'm converting from a DB2 VIEW here... orig was:

    select count(distinct(tbuser.fk_userid))

    thank you very much..
    Frances

    Frances Guest

  2. #2

    Default Re: distinct, count

    Frances wrote:
    > hello, is there something wrong w/this query?
    >
    > SELECT DISTINCT(tbuser.fk_userid), COUNT(tbuser.fk_userid) AS
    > cuserid........
    >
    > I'm getting this error: Unknown table 'tbuser' in field list
    > (table is there, use in other queries..)
    >
    > I'm converting from a DB2 VIEW here... orig was:
    >
    > select count(distinct(tbuser.fk_userid))
    >
    > thank you very much..
    > Frances
    pls, what does it mean that a table doesn't exist "in field list"?
    first time I see this..
    thanks again..

    Frances Guest

  3. #3

    Default Re: distinct, count

    Peter H. Coffin wrote:
    > On 2 Dec 2005 16:41:31 +0100, Frances wrote:
    >
    >>hello, is there something wrong w/this query?
    >>
    >>SELECT DISTINCT(tbuser.fk_userid), COUNT(tbuser.fk_userid) AS
    >>cuserid........
    >>
    >>I'm getting this error: Unknown table 'tbuser' in field list
    >> (table is there, use in other queries..)
    >>
    >>I'm converting from a DB2 VIEW here... orig was:
    >>
    >>select count(distinct(tbuser.fk_userid))
    >>
    >>thank you very much..
    >
    >
    > You might want to put a FROM clause in there....
    >
    > SELECT COUNT(DISTINCT fk_userid) FROM tbuser;
    >
    thank you.. I had posted only partial query as entire query was a
    bit long.. (question was about that part of query only....;) I hope
    this makes sense, I do know basic MySQL, but when get into more complex
    queries I get a bit lost..) problem has been solved..
    still would like to know what it means when it says a table can't be
    found in "field list" thank you very much for your response...
    Frances Guest

  4. #4

    Default Re: distinct, count

    Frances wrote:
    > hello, is there something wrong w/this query?
    >
    > SELECT DISTINCT(tbuser.fk_userid), COUNT(tbuser.fk_userid) AS
    > cuserid........
    >
    > I'm getting this error: Unknown table 'tbuser' in field list
    > (table is there, use in other queries..)
    I can't tell exactly the problem, because I don't know the rest of your
    query. Is tbuser in the FROM clause of your SELECT? Are you querying a
    view which itself references tbuser? If so, you should reference the
    view name in this query, not the underlying table name. Or is the above
    query your definition of the view? A few more details are needed to
    diagnose this one.

    Another problem is that a leading DISTINCT in the select-list is not a
    function; it's a modifier to the whole query. You don't use it like:
    DISTINCT(expr). You use it like:
    SELECT DISTINCT field(s) ...
    It applies to _all_ the fields you are selecting.

    A different context for DISTINCT is that which is used inside a COUNT()
    function. This is not the same DISTINCT operation as mentioned above.
    Again, it is not used in the syntax as though it were a function. You
    can use it like:
    COUNT(DISTINCT expr)
    It applies only to its expression argument, for purposes of calculating
    the COUNT.

    DISTINCT can be used similarly to modify the expression inside SQL
    aggregate functions AVG(), MIN(), MAX(), and SUM(), and the special
    MySQL extension GROUP_CONCAT().

    It is a bit muddy, because expr can have parentheses around it; that is,
    "2+2" and "(2+2)" are both legal and equivalent expressions. So
    COUNT(DISTINCT (expr)) is syntactically legal, although the inner
    parentheses are not necessary.

    Regards,
    Bill K.
    Bill Karwin Guest

Similar Threads

  1. SQL Distinct
    By DDhillon in forum Coldfusion Database Access
    Replies: 8
    Last Post: July 27th, 03:44 AM
  2. Needed, COUNT(DISTINCT *) workaround for ACCESS
    By rickaclark54 in forum Macromedia ColdFusion
    Replies: 4
    Last Post: March 14th, 08:07 PM
  3. Replies: 3
    Last Post: April 18th, 12:52 PM
  4. Replies: 0
    Last Post: April 15th, 01:22 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