Need Help With Two Queries

[Susan]

Customer Orders are ranked from 1 to 10 and are assigned an order type of 1,
2 or 3. The table for this is:
TblOrderRankTyp e
OrderRankTypeID
CustomerID
Rank
Type

I need help with two queries, please.

1. Count Customers(Custo merID) by Rank who placed Type 1 or Type 2 orders
and never placed a Type 3 order.

2. Count Customers(Custo merID) by Rank who placed all Type 3 orders and
never placed a Type 1 or Type 2 order.

I'm having problems excluding Type 3 in 1 and excluding Type 1 or Type 2 in
2.

Thank you!

Susan

[John Baker]

Susan wrote:

Customer Orders are ranked from 1 to 10 and are assigned an order type of 1,
2 or 3. The table for this is:
TblOrderRankTyp e
OrderRankTypeID
CustomerID
Rank
Type 1. Count Customers(Custo merID) by Rank who placed Type 1 or Type 2 orders
and never placed a Type 3 order.

2. Count Customers(Custo merID) by Rank who placed all Type 3 orders and
never placed a Type 1 or Type 2 order.

I'm having problems excluding Type 3 in 1 and excluding Type 1 or Type 2 in
2.

You might be able to do each one of these queries in a single step, but
I prefer to keep things simple, both for myself and for those who might
come after me.

I would do both of these queries in two stages. IE - for #1, do this first:

qryHasType3
select distinct CustomerId from tblOrderRankTyp e where Type = 3

then

qryHas1or2ButNo 3
select distinct CustomerId from tblOrderRankTyp e
where (Type = 1 or Type = 2) and
CustomerID not in (select CustomerId from qryHasType3)

The second one should follow easily for you.

[John Winterbottom]

"Susan" <sm*****@earthl ink.net> wrote in message
news:sU******** *********@newsr ead3.news.atl.e arthlink.net...

Customer Orders are ranked from 1 to 10 and are assigned an order type of
1,
2 or 3. The table for this is:
TblOrderRankTyp e
OrderRankTypeID
CustomerID
Rank
Type

I need help with two queries, please.

1. Count Customers(Custo merID) by Rank who placed Type 1 or Type 2
orders
and never placed a Type 3 order.
something like this (air code)

select c.custRank, count(*) as customerCount
from customers as c
where
(c.custType = 1 or c.custType = 3)
and not exists
(
select * from customers as c2
where c2.customerID = c.customerID
and c2.custType = 3
)
group by c.custRank
order by c.custRank
You can use the same logic for the second query. You'll need to substitute
your own table / column names as well.

2. Count Customers(Custo merID) by Rank who placed all Type 3 orders and
never placed a Type 1 or Type 2 order.

I'm having problems excluding Type 3 in 1 and excluding Type 1 or Type 2
in
2.

Thank you!

Susan

[Susan]

John,

Thank you very much for your response!

Is that a typo in the first Where clause - should the second Type be 2 not
3? Also, directly following did you mean by Not Exists Not In?

I tried your SQL and it doesn't quite work the way I need. Some customers
place some orders of Type 1 and other orders of Type 2. I need these
customers to be counted once. Some customers place Type 1 orders, some Type
2 orders and other customers sometimes Type 1 and other times Type 2. Your
SQL counts the latter customers twice. Can you suggest a revised SQL.

Thanks, John!

Susan
"John Winterbottom" <as******@hotma il.com> wrote in message
news:2s******** *****@uni-berlin.de...

"Susan" <sm*****@earthl ink.net> wrote in message
news:sU******** *********@newsr ead3.news.atl.e arthlink.net...

Customer Orders are ranked from 1 to 10 and are assigned an order type of 1,
2 or 3. The table for this is:
TblOrderRankTyp e
OrderRankTypeID
CustomerID
Rank
Type

I need help with two queries, please.

1. Count Customers(Custo merID) by Rank who placed Type 1 or Type 2
orders
and never placed a Type 3 order.

something like this (air code)

select c.custRank, count(*) as customerCount
from customers as c
where
(c.custType = 1 or c.custType = 3)
and not exists
(
select * from customers as c2
where c2.customerID = c.customerID
and c2.custType = 3
)
group by c.custRank
order by c.custRank
You can use the same logic for the second query. You'll need to substitute
your own table / column names as well.

2. Count Customers(Custo merID) by Rank who placed all Type 3 orders and
never placed a Type 1 or Type 2 order.

I'm having problems excluding Type 3 in 1 and excluding Type 1 or Type 2
in
2.

Thank you!

Susan