How to group by and select

Question

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

So your desired result is any row that has either 'A' or 'a' on both customer and type column? If so then just: SELECT customer, type FROM tablename WHERE customer = 'A' OR type = 'a' — Albert
– Albert, Commented Jul 30, 2020 at 6:43
You want to select from the table where a certain condition is met (WHERE clause). The condition is that there exists a row with a certain type for the customer in the table (EXISTS clause). There are other solutions, but this is the straight-forward way. — Thorsten Kettner
– Thorsten Kettner, Commented Jul 30, 2020 at 6:44
Please add the rules (aka conditions) you want to apply here. Otherwise you'll get wild guessing and guessed results. — Chrᴉz remembers Monica
– Chrᴉz remembers Monica, Commented Jul 30, 2020 at 6:48
Just to get this straight: Using an IN cause is as much straight-forward as an EXISTSclause of course (and I even like it better for its simplicity). The wording sounds a little more complicated, while the query is actually shorter than the query with an exists clause: The condition is that the customer is 'in* the set of customers with the desired type (IN clause). — Thorsten Kettner
– Thorsten Kettner, Commented Jul 30, 2020 at 6:53

Tim Biegeleisen · Accepted Answer · 2020-07-30 07:48:47Z

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.

Thanks, I'd like to understand in subquery WHERE t2.customer = t1.customer AND t2.type = 'a' why this work well .. it seems 'customer ='A' and type='c' ' will be droped..

Arijit Kanrar · Accepted Answer · 2020-07-30 06:48:21Z

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

Wasim Ansari · Accepted Answer · 2020-07-30 06:50:10Z

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

Amir Kadyrov · Accepted Answer · 2020-07-30 07:25:08Z

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

Collectives™ on Stack Overflow

How to group by and select

4 Answers 4

2 Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Related