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******@hotmail.com> wrote in message
news:2s*************@uni-berlin.de...
"Susan" <sm*****@earthlink.net> wrote in message
news:sU*****************@newsread3.news.atl.earthl ink.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:
TblOrderRankType
OrderRankTypeID
CustomerID
Rank
Type
I need help with two queries, please.
1. Count Customers(CustomerID) 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(CustomerID) 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