CODEWITHSUNDEEP
sql-servert-sql
Al Lee
Al Lee

Reputation: 3

How can I get T-SQL to show a value that doesn't occurs more than once

I'm sorry, I'm newbie using T-SQL AND I would like to know how can i get value that doesn't occurs more than once. I already tried this but it didn't work.

SELECT DISTINCT * 
FROM Orders 
WHERE PmtType  NOT IN ('VISA','DISC','FUNDING','DEALER CHECK','MC'
                       ,'AMEX','BONUS POOL','DLR CK - NET30'
                       ,'WIRE','MO','EXCHANGE','ONLINE','NULL')  
   AND OrderDate BETWEEN '2014-03-01 00:00:00' 
                          and '2015-03-01 00:00:00' 
   AND CompanyId  IN ('1311','8390','8394','8396','8397','8399','3966',
                     '8407','8408','8315','8411','8413','8414','8416'
                     ,'8419','4850','8426','8428','8429','8430')

What I'm trying to get is this. Companies that receive Free Demos. Which the PmtType would be free, but never purchased a product.

If the customer never purchase a product the customer Id shouldn't appear in the 

PmtType IN ('VISA','DISC','FUNDING','DEALER CHECK'
            ,'MC' ,'AMEX','BONUS POOL','DLR CK - NET30'
            ,'WIRE','MO','EXCHANGE','ONLINE','NULL')

Upvotes: 0

Views: 61

Answers (1)

Daniel E.
Daniel E.

Reputation: 2059

if i read the question correctly you want to know which IDs have only 1 order, this will do it. I used generic field name as you didnt specify what "value" you want to find...

EDIT: added the NOT EXISTS clause after OP Comment, you may no longer need the group by, that is up to you...

SELECT CompanyId --add fields here as needed.
     ,Count(*) [Occurences]
FROM Orders o
WHERE PmtType = 'FREE'
AND NOT EXISTS (SELECT CompanyId 
                FROM Orders io 
                WHERE o.CompanyId = io.CompanyId 
                    AND PmtType <> 'FREE' )
GROUP BY CompanyId --add fields here as needed.
HAVING Count(*) = 1  --leave this out to see how many free demos each company got.

Upvotes: 1

Related Questions