Mombu the Programming Forum sponsored links

Go Back   Mombu the Programming Forum > Programming > distinct count
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 8th December 20:47
frances
External User
 
Posts: 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
  Reply With Quote


  sponsored links


2 8th December 20:50
frances
External User
 
Posts: 1
Default distinct count


pls, what does it mean that a table doesn't exist "in field list"?
first time I see this..
thanks again..
  Reply With Quote
3 8th December 20:51
peter h coffin
External User
 
Posts: 1
Default distinct count


You might want to put a FROM clause in there....

SELECT COUNT(DISTINCT fk_userid) FROM tbuser;

--
63. Bulk trash will be disposed of in incinerators, not compactors. And they
will be kept hot, with none of that nonsense about flames going through
accessible tunnels at predictable intervals.
--Peter Anspach's list of things to do as an Evil Overlord
  Reply With Quote
4 8th December 20:51
frances
External User
 
Posts: 1
Default distinct count


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...
  Reply With Quote
5 8th December 20:52
bill karwin
External User
 
Posts: 1
Default distinct count


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.
  Reply With Quote


  sponsored links


Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666