On Mon, 19 May 2014 21:58:58 -0500
Wendy <[email protected]> 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
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
