CODEWITHSUNDEEP
sqlselect
pm100
pm100

Reputation: 50210

Get a count of rows not matching criteria

Given a simple table (order detail/history kind of thing) with a customer and a product:

+--------------------+
| customer | product |
+--------------------+
| Smith    | p1      |
| Smith    | p3      |
| Jones    | p1      |
| Jones    | p2      |
| Davis    | p3      |
| Davis    | p9      |
| Brown    | p1      |
| Brown    | p2      | 
| Brown    | p5      |
+----------+---------+

I want to list all customers that have never ordered product p1, i.e. Davis in the above data set.

This is where i started but, of course, it doesnt work and I can't think of where to go next:

select 
    customer,
    count(*) as c 
where product='p1' 
    and c = 0

Upvotes: 1

Views: 550

Answers (3)

Allan Chua
Allan Chua

Reputation: 10185

You can also use this approach

 SELECT t.CustomerName
 FROM Table t
 WHERE NOT EXISTS (SELECT 1 
                   FROM Table t2 
                   WHERE t2.CustomerName = t.CustomerName
                   AND t2.ProductName = 'p1')

Upvotes: 0

Corey Adler
Corey Adler

Reputation: 16149

Try this out:

select customer
from MyTable
where customer not in (select customer from MyTable where Product = 'P1')

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270733

Here is one way, using an aggregation query:

select customer
from t
group by customer
having sum(case when product = 'p1' then 1 else 0 end) = 0

This gives you all the customers in the table. If you have a separate list of customers, then you might use:

select customer
from customerTable
where customer not in (select customer from t where product = 'p1')

Upvotes: 0

Related Questions