Hi.
On Tue, Oct 02, 2001 at 01:02:19PM +0100, [EMAIL PROTECTED] wrote:
> >Hi there. I have problems with mySQL
>
> >What I would like to do is:
>
> >I have a statement
>
> >SELECT a.id AS ID, IDmark, IDrecipe, ocenjevalec, ocena,
> >ROUND(AVG(ocena),1) as povprecje, COUNT(*) as all, IF (ID=ocenjevalec,
> >'yes', 'no') as zeocenil
> >FROM ocenerecepti, obiskovalci
> >WHERE IDrecept = 1365 group by IDrecipe
>
> >But 'zeocenil' allways returns no, because it groups all the data together
> >and only the first data that has IDrecept = 1365 is checked with if sentance.
>
> >I have to use group by because of AVG and count. Is there any if-like
> >function that checks ALL records for the ID=ocenjevalec, not just the first
> >(or the grouped by, seems to take 'ocenjevalec' as from the first data).
It may be randomly chosen, but most often is the first.
> >this is not possible, how can I achieve that? Should I make another
> >recordset?
You can use the fact that boolean expression in MySQL evaluate to 1
resp. 0 if used in an integer context, and use SUM() to force
evaluation of all of them:
IF( SUM( ID=ocenjevalec ) > 0 ,'yes', 'no') as zeocenil
which is a short form for the more obvious
IF( SUM( IF(ID=ocenjevalec,1,0) ) > 0 ,'yes', 'no') as zeocenil
Bye,
Benjamin.
--
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
