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 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..

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 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.