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

Reply via email to