This also works...a little mod to Igor's...
You need to ensure that the players are always listed in the same 1,2 order
though. Otherwise the group by won't work.
.mode column
.width 8
create table Games(id,player1,player2,score);
insert into Games values (1,1,2,1);
insert into Games values (2,1,2,-1);
insert into Games values (3,1,3,1);
insert into Games values (4,1,3,1);
insert into Games values (5,2,3,-1);
insert into Games values (6,2,3,-1);
select
player1,player2,count(*) TotalGames,
sum(score > 0) GamesWonByPlayer1,
sum(score < 0) GamesWonByPlayer2,
sum(score = 0) Draws
from Games group by player1,player2;
player1 player2 TotalGames GamesWonByPlayer1 GamesWonByPlayer2 Draws
-------- ---------- ---------- ----------------- -----------------
----------
1 2 2 1 1 0
1 3 2 2 0 0
2 3 2 0 2 0
Michael D. Black
Senior Scientist
Advanced Analytics Directorate
Northrop Grumman Information Systems
________________________________
From: sqlite-users-boun...@sqlite.org on behalf of Ian Hardingham
Sent: Tue 11/16/2010 7:31 AM
To: General Discussion of SQLite Database
Subject: EXTERNAL:Re: [sqlite] Query help - two similar queries
Many thanks again Igor.
On 16/11/2010 13:15, Igor Tandetnik wrote:
> Ian Hardingham<i...@omroth.com> wrote:
>> Thanks Igor. Can i get custom results like
>>
>> GamesWonByPlayer1
>>
>> By using getColumn in the normal way?
> I'm not familiar with the term "custom result". GamesWonByPlayer1 is just a
> column alias which a) is completely optional, you could safely drop it from
> the query (I've just included it for clarity), and b) does not in any way
> interfere with sqlite3_column_* API (on which, I guess, getColumn is based in
> whatever language binding you are using).
>
>> That may be a stupid question - I guess what I mean is, are those custom
>> identifiers treated as column names when reading back from the select?
> Basically, yes. With SQLite, you could address a column by name or by
> position (numbered left to right starting from 0). Column alias in the query
> makes it more convenient to do the former.
_______________________________________________
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
