Select Records With Only One Value In A Column Where Multiple Are Possible

Question

My apologies in advance, this is probably a basic question asked and answered but I don't know how to word the search to find the right results.

I have a table that (among other columns) contains program names for a customer number. I need to identify customers that have only one specific program and no others. A simplified example:

Col1 = Customer_Number, Col2 = Program_Name

Customer 1 has three records because they are enrolled in 2013BA1111, 2013BO1161 and 2013BO1163. Customer 2 has just one record because they are only enrolled in 2013BA1111.

Using Teradata SQL Assistant, if I select WHERE Program_Name = '2013BA1111', both Customer 1 and Customer 2 will be returned since they are both enrolled in program 2013BA1111. I want to select only Customer 2 since they have ONLY 2013BA1111.

Thanks!

score 6 · Accepted Answer · answered Feb 11 '14 at 17:58

In standard (ANSI/ISO) SQL, a derived table is your friend. Here, we join the customer table against a derived table that produces the list of customers having only 1

select *
from customer c
join ( select customer_id
       from customer
       group by customer_id
       having count(program_name) = 1
     ) t on t.customer_id = c.customer_id
where ... -- any further winnowing of the result set occurs here

Aaron Palmer · Answer 2 · 2014-02-11T17:54:03.063

5

Perhaps something like this:

select Customer_Number, Program_Name
from YourTable t1
left join (
    select Customer_Number
    from YourTable t2
    where t2.Program_Name <> '2013BA1111'
) t3 on t1.Customer_Number = t3.Customer_Number
where 
t1.Program_Name = '2013BA1111'
and t3.Customer_Number is null

The outer query is selecting all records that have the given Program_Name, then it is joined with an inner query of everyone who has a record that does not equal the given Program_Name, and the outer query checks to make sure that the joined inner query doesn't have a match.

edited Feb 11 '14 at 17:54

answered Feb 11 '14 at 17:49

Aaron Palmer

8,912
9
48
77

I would presume '2013BA1111' is just an example – OlleR Feb 11 '14 at 17:51
Correct. It is the one the OP used in his post. – Aaron Palmer Feb 11 '14 at 17:52
Yes, but I mean that user3298276 don't want to get the specific row. Any row that only occurs once is whats asked for I presume – OlleR Feb 11 '14 at 17:54
By the OP statement "I need to identify customers that have only one specific program and no others" I assumed they had a specific program in mind to search for. I may have misunderstood. – Aaron Palmer Feb 11 '14 at 17:56
Correct, in this instance I would want to identify customers with only '2013BA1111'. It is the base program that all customers should have but I see utility in being able to replace it with any program name(s) in the future. Thanks for the help so far, I'll get back to my query this afternoon and try some of these. – user3298276 Feb 11 '14 at 18:00

score 1 · Answer 3 · answered Feb 11 '14 at 17:50

1

I'm unfamiliar with "Teradata", but you should be able to do it in sql with something like:

SELECT 
     Col1 AS Customer,
     COUNT(*) AS TotalOccurences
FROM
     YourTable
GROUP BY Col1
HAVING COUNT(*) = 1

answered Feb 11 '14 at 17:50

b.runyon

144
5

This works, but only for 2013BA1111 since all customers have this program. If I were concerned about another program, it may not - but this does answer my immediate need too...Thanks! – user3298276 Feb 12 '14 at 17:57

score 0 · Answer 4 · answered Feb 11 '14 at 17:49

0

This would do it in SQL server. Not sure if Teradata is the same...

select t.Col1, min(t.Col2) from myTable t
group by t.Col1
having count(*) = 1

answered Feb 11 '14 at 17:49

OlleR

252
1
9

This works, but only for 2013BA1111 since all customers have this program. If I were concerned about another program, it may not - but this does answer my immediate need too...Thanks! – user3298276 Feb 12 '14 at 17:57

score 0 · Answer 5 · answered Feb 11 '14 at 19:53

0

Do you need only the customer_number or additional columns?

SELECT Customer_Number
FROM tab
GROUP BY 1
HAVING SUM(CASE WHEN Program_Name = '2013BA1111' THEN 0 ELSE 1 END) = 1;

SELECT *
FROM tab AS t1
WHERE Program_Name = '2013BA1111' 
AND NOT EXISTS
 (
   SELECT * FROM tab AS t2 
   WHERE t1.Customer_Number = t2.Customer_Number
     AND Program_Name <> '2013BA1111'
 );

SELECT *
FROM tab
QUALIFY
   SUM(CASE WHEN Program_Name = '2013BA1111' THEN 0 ELSE 1 END) 
   OVER (PARTITION BY Customer_Number) = 1;

answered Feb 11 '14 at 19:53

dnoeth

59,503
4
39
56

While this may work if I had that need, it's getting a little beyond my comprehension. I could probably spend some time dissecting to figure it out, but I think some of the simpler answers are sufficient for now. Thanks! – user3298276 Feb 12 '14 at 17:55

Select Records With Only One Value In A Column Where Multiple Are Possible

5 Answers5