On Mon, 19 May 2014 21:58:58 -0500 Wendy <wmpa...@gmail.com> wrote: > I'm still not getting the results I need for this query: anyone with > any idea, help greatly appreciated. > > "SELECT AwayTeam As 'Team', CASE WHEN (AwayTeamScore > HomeTeamScore) > THEN 3 WHEN (AwayTeamScore=HomeTeamScore) THEN 1 ELSE 0 END AS > 'Points' FROM Results UNION ALL SELECT HomeTeam As 'Team', CASE WHEN > (HomeTeamScore > AwayTeamScore) THEN 3 WHEN > (HomeTeamScore=AwayTeamScore) THEN 1 ELSE 0 END AS 'Points' FROM > Results GROUP BY Team
Let's format it so we can both read it: SELECT AwayTeam As 'Team', CASE WHEN (AwayTeamScore > HomeTeamScore) THEN 3 WHEN (AwayTeamScore=HomeTeamScore) THEN 1 ELSE 0 END AS 'Points' FROM Results UNION ALL SELECT HomeTeam As 'Team', CASE WHEN (HomeTeamScore > AwayTeamScore) THEN 3 WHEN (HomeTeamScore=AwayTeamScore) THEN 1 ELSE 0 END AS 'Points' FROM Results GROUP BY Team The first SELECT yields 0, 1, or 3 for each AwayTeam. So far, so good. The second SELECT might be making the mistake of trying to GROUP BY an alias. Even if that worked in SQLite -- which I don't think it does -- you really want to refer to the column by its name. But you don't meant to group only the second part, do you? You may want select Team, sum(Points) as Points from ( SELECT AwayTeam As 'Team', CASE WHEN (AwayTeamScore > HomeTeamScore) THEN 3 WHEN (AwayTeamScore=HomeTeamScore) THEN 1 ELSE 0 END AS 'Points' FROM Results UNION ALL SELECT HomeTeam As 'Team', CASE WHEN (HomeTeamScore > AwayTeamScore) THEN 3 WHEN (HomeTeamScore=AwayTeamScore) THEN 1 ELSE 0 END AS 'Points' FROM Results ) as T GROUP BY Team HTH. --jkl _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users