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
