sqlite - Syntax for Select in SQL Query -

i have sqlite database following table:

create table games (name text, date text, winloss int, gameid int, pointsfor int, pointsagainst int); 

two sample records like:

anna a, 7/12/13, 0, 345, 56, 28 barley b, 7/12/13, 1, 345, 28, 56 

(barley's team lost, , anna's won. each game has several players on each team.) want create query return games had x players on 1 team , y players on another, plus cumulative result of games.

i know how using perl , csv file, , believe use same method dbi interface. want learn how create report using sql queries, however. newcomer sql, , suspect solution may involve pivoting table using case when or join create new table; can't see how it.

this query return games players on same team , won (or lost, depending on value of winloss):

select gameid,date games name in ('anna a', 'barley b') , winloss=1  group gameid  having count(*)>1; 

but don't know how generalize query return games players on other team.

this give lines game or games , b won versus c, d , e.

select *  games  gameid in         (select gameid games         name in ('anna a', 'barley b') , winloss=1          group gameid          having count(*) = 2     intersect      select gameid games         name in ('charly c', 'dave d', 'ed e') , winloss = 0          group gameid          having count(*) = 3) ; 

alternatively, use :

select * games gameid in (     select gameid games name = 'anna a'   , winloss = 1   intersect     select gameid games name = 'barley b' , winloss = 1   intersect      select gameid games name = 'charly c'   , winloss = 0 intersect     select gameid games name = 'dave d' , winloss = 0     intersect     select gameid games name = 'ed e' , winloss = 0     ) ; 

whichever suits best.

you can add sum , group by cumulative results.