----- Original Message ----- From: "Downey, Shawn" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Thursday, September 30, 2004 7:26 PM Subject: [sqlite] complex query question
> The query should return exactly 3 records: > > Hoyhoy | 2 | 1 | 1 > Fred | 0 | 2 | 0 > Tony | 1 | 1 | 0 select distinct name , sum(time1!='X') time1 , sum(time2!='X') time2 , sum(time3!='X') time3 from times group by name > Alternatively we could use: > > Hoyhoy | T | T | T > Fred | F | T | F > Tony | T | T | F select distinct name , substr('FT' , max(time1!='X')+1 , 1) time1, substr('FT' , max(time2!='X')+1 , 1) time2, substr('FT' , max(time3!='X')+1 , 1) time3 from times group by name > Or 0/1 since the exact count is unimportant. select distinct name , max(time1!='X') time1, max(time2!='X') time2, max(time3!='X') time3 from times group by name All tested on release 2.8.12 . Take your pick. Lawrence --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.771 / Virus Database: 518 - Release Date: 28/09/04