Hi,
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
Have also tried with bracketing:
"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
Data should be shown aggregated:
A 3
B 1
C 4
D 6 for example....
Still getting....
A 1
B 1
D 3
A 1
C 1
D 1
A 1
D 1
D 1 etc.
Many thanks
Wendy
On Thu, May 15, 2014 at 5:56 AM, E.Pasma <pasm...@concepts.nl> wrote:
> Op 15 mei 2014, om 12:09 heeft E.Pasma het volgende geschreven:
>
>
> Op 14 mei 2014, om 21:44 heeft Wendy het volgende geschreven:
>>
>> Hi,
>>> Wondering if anyone can help me with the following:
>>>
>>>
>>> -
>>>
>>> Does anyone know how I can get the SUM() aggregate function within this
>>> SQLite statement?
>>>
>>> SELECT AwayTeam As 'Team', CASE WHEN AwayTeamScore > HomeTeamScore THEN
>>> 3 WHEN AwayTeamScore=HomeTeamScore THEN 1 ELSE 0 END AS 'Points' FROM
>>> Results UNION SELECT ALL HomeTeam As 'Team', CASE WHEN HomeTeamScore >
>>> AwayTeamScore THEN 3 WHEN HomeTeamScore=AwayTeamScore THEN 1 ELSE 0
>>> END AS
>>> 'Points' FROM Results ORDER BY Team"
>>>
>>> If I put it in each of the Select statements it doesn't give me the
>>> results expected; without the SUM in the statement, it's showing me each
>>> row as it should be, but I want to aggregate the results by TEAM.
>>>
>>> E.g.
>>>
>>> TEAM A 4 ( two records A-1 A-3)
>>>
>>> TEAM B 3 (one record B-3)
>>>
>>> TEAM C 0
>>>
>>> TEAM D 4 (two records D-1 D-3)
>>>
>>> Thanks for any suggestions, this is the last part I'm a bit stuck on.
>>>
>>
>> Hi, sounds like a piece of cake: put the "union" statement inside
>> brackets and treat it as sub-query, like:
>>
>> select "Team", sum("Points")
>> from (<the original union query>)
>> group by "Team"
>>
>> Or, if you have the latest SQLite versions, there is an equivalent Common
>> Table Expression:
>>
>> with basequery as (<the original union query>)
>> select "Team", sum("Points")
>> from basequery
>> group by "Team"
>>
>>
>> Hope this will work, E Pasma
>>
>
> PS: your query states: ..UNION SELECT ALL.. and I don't know if this is
> alright. Should it not be '.. UNION ALL SELECT ..'? You can test this with
> a case where you have two identical records. e.g. two records A-1 A-1). A
> plain union (without all) implies distinct.
>
> _______________________________________________
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
