|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-11-05 : 22:37:10
|
TOAST writes "Heya SQL guru.
I'm the sort of person who likes to do EVERYTHING in one query.
Rather than try to explain the whole thing what I'm trying to do, I'll show you the gear.
I have (say) a customer table. Some customers just have a look around, and some make purchases. I'd like to find out who makes purchases and who just browses. Check this out:
create table customer
(
customer_id integer not null identity(1,1) primary key,
first_name varchar(30) not null,
made_purchase char(1) not null default 'n' check (made_purchase in ('y', 'n')),
country varchar(30),
)
insert into customer(first_name, made_purchase, country) values ('Bob','y','USA')
insert into customer(first_name, made_purchase, country) values ('Bob3','y','USA')
insert into customer(first_name, made_purchase, country) values ('Bob2','n','USA')
insert into customer(first_name, made_purchase, country) values ('Bo1b','y','USA')
insert into customer(first_name, made_purchase, country) values ('Bob4','y','USA')
insert into customer(first_name, made_purchase, country) values ('B1ob','n','USA')
insert into customer(first_name, made_purchase, country) values ('Bob4','n','USA')
insert into customer(first_name, made_purchase, country) values ('B3ob4','n','Canada')
insert into customer(first_name, made_purchase, country) values ('Bo5b4','n','Canada')
insert into customer(first_name, made_purchase, country) values ('Bo2b4','y','Canada')
insert into customer(first_name, made_purchase, country) values ('B1ob4','y','Canada')
insert into customer(first_name, made_purchase, country) values ('Bo4b4','n','Canada').
Now I'd like to find out who visited the site, and who made purchases, grouped by country. The query looks something like this:
select country, count(customer_id) as 'paying customers'
from customer
where made_purchase='y'
group by country
But if I want to put another group_by in I get in a mess:
select c.country,
count(c1.customer_id) as 'total customers', count(c2.customer_id) as 'paying customers'
from customer c, customer c1, customer c2
where c2.made_purchase='y'
and c.customer_id = c1.customer_id
and c.customer_id = c2.customer_id
and c1.customer_id = c2.customer_id
group by c.country
Gives incorrect results.
Any ideas ? Is this actually possible ?" |
|
Several Aggregates in one query.