Need Help With Two Queries

  • Thread starter Thread starter Susan
  • Start date Start date
S

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

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
 
Susan said:
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
Click to expand...
1. Count Customers(CustomerID) by Rank who placed Type 1 or Type 2 orders
and never placed a Type 3 order.

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.
Click to expand...


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 tblOrderRankType where Type = 3

then

qryHas1or2ButNo3
select distinct CustomerId from tblOrderRankType
where (Type = 1 or Type = 2) and
CustomerID not in (select CustomerId from qryHasType3)

The second one should follow easily for you.
 
Susan said:
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.
Click to expand...

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.
 
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
 
Back
Top