----- 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
