CODEWITHSUNDEEP
sqloracle-database
Heisenberg
Heisenberg

Reputation: 5279

How to group by and select

I tried to extract customer who has type a

I guess I must group by in customer and tried to having in type

customer type
A         a  
A         c
B         b 
B         c
C         a
C         a

but I couldn't figure out specific way to achieve this. If someone has opinion,please let me know.

My desired result is following

customer type
A        a
A        c
C        a
C        a

Thanks

Upvotes: 1

Views: 71

Answers (4)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

Using exists, we can try:

SELECT t1.customer, t1.type
FROM yourTable t1
WHERE EXISTS (SELECT 1 FROM yourTable t2 WHERE t2.customer = t1.customer AND t2.type = 'a');

The exists logic reads in plain English as select any record for which we can find at least one record for the same customer whose type is a. This means retain all customer records, where at least one of those records has type a.

Upvotes: 2

Amir Kadyrov
Amir Kadyrov

Reputation: 1288

Do not use accessing table twice. Use window functions instead.

with t(customer, type) as (
select 'A', 'a' from dual union all
select 'A', 'c' from dual union all
select 'B', 'b' from dual union all 
select 'B', 'c' from dual union all
select 'C', 'a' from dual union all
select 'C', 'a' from dual)
select customer, type
from 
   (select t.*, count(decode(type, 'a', 1)) over (partition by customer) cnt
    from t
   )
where cnt > 0;

CUSTOMER TYPE
-------- ----
A        a
A        c
C        a
C        a

Upvotes: 0

Wasim Ansari
Wasim Ansari

Reputation: 290

I hope the below query completes your requirement.

SELECT 
    *
FROM
    test.customer
WHERE
    customer IN (SELECT 
            customer
        FROM
            test.customer
        WHERE
            type = 'a');

Output:

A   a
A   c
C   a
C   a

Upvotes: 1

Arijit Kanrar
Arijit Kanrar

Reputation: 450

You don't need to group. You can just filter the table for customers that have type 'a' and use that resultset to filter the table again for the customers.

WITH
    cust_data
    AS
        (SELECT 'A' AS customer, 'a' AS TYPE FROM DUAL
         UNION ALL
         SELECT 'A' AS customer, 'c' AS TYPE FROM DUAL
         UNION ALL
         SELECT 'B' AS customer, 'b' AS TYPE FROM DUAL
         UNION ALL
         SELECT 'B' AS customer, 'c' AS TYPE FROM DUAL
         UNION ALL
         SELECT 'C' AS customer, 'a' AS TYPE FROM DUAL
         UNION ALL
         SELECT 'C' AS customer, 'a' AS TYPE FROM DUAL)
SELECT *
FROM cust_data c
WHERE customer IN (SELECT customer
                   FROM cust_data
                   WHERE TYPE = 'a');

This gives

CUSTOMER    TYPE
A           c
A           a
C           a
C           a

Upvotes: 1

Related Questions