Re: [sqlite] Select from Select

2003-10-19 Thread Michael A. Cleverly 
On Sun, 19 Oct 2003, Brian Pugh wrote:

> How many records in a certain Postcode area (Zip code to our
> US friends!);
> How many of those have telephones (ie, the field is blank if
> they don't have a telephone);
> How many with telephones can be canvassed (I have a field called
> Canvass that has either a 'Y' or 'N' in there)
 
> Table is called NewsData; Postcode field is called Postcode;
> Telephone field is called Tel; Canvass field is called Canvass

Try something like:

select postcode, count(*) as num_records, 
   sum(case when tel is not null then 1 else 0 end) as num_phones,
   sum(case when tel is not null and canvas='Y' then 1 else 0 end) as 
   num_canvasable
  from NewsData
 group by postcode;

Michael


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

[sqlite] Select from Select

2003-10-19 Thread Brian Pugh 
Hi,

I would like to be able to run a query on my names and addresses
database, returning the following:

How many records in a certain Postcode area (Zip code to our
US friends!);
How many of those have telephones (ie, the field is blank if
they don't have a telephone);
How many with telephones can be canvassed (I have a field called
Canvass that has either a 'Y' or 'N' in there)

I can do the first (how many records), but I don't want to have
to run a query for every return needed. I think a "select from
select" is needed, but I don't know how to code it

Table is called NewsData; Postcode field is called Postcode;
Telephone field is called Tel; Canvass field is called Canvass

Many thanks for any help,

Brian Pugh