From: Steffan A. Cline
Date: August 13 2006 9:33am
Subject: Group by with an IF
List-Archive: http://lists.mysql.com/mysql/200902
Message-Id: <C1043FFA.40C25%steffan@hldns.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="US-ASCII"
Content-Transfer-Encoding: 7bit

I have the following query:

select *, 
if( season_week_date = "2006-08-16", "on", "off" ) as stat,
sum(overall_points) as total_points
from rosters r 
left join celebs c 
on c.celeb_id = r.celeb_id
where 
season_id=5062
and user_id=1
group by r.celeb_id
order by overall_rank, ln, fn;

It almost works as expected however the problem I am concerned about is that
I never get a stat saying "On" after it resolves. Removing the sum() and
adding group I get the total_points as expected but now the results are
always off.

Can anyone shed any light on this one?


Thanks

Steffan

---------------------------------------------------------------
T E L  6 0 2 . 5 7 9 . 4 2 3 0 | F A X  6 0 2 . 9 7 1 . 1 6 9 4
Steffan A. Cline
Steffan@stripped                             Phoenix, Az
http://www.ExecuChoice.net                                  USA
AIM : SteffanC          ICQ : 57234309
                                  Lasso Partner Alliance Member
---------------------------------------------------------------