as per sum per heading my question
I am stumped to find query statement in my ORACLE SQL Developer for shorten time as I have huge database and it take time to sorting by individual column with where and join table on each clause statement however it take two hours result outcome differs to testing for one profile customer output which is quicker than whole database.
Purchased table has customer purchased car, so I am trying to get one answer to find out if customer has particular RED colour any car type to output result column regardles past and present transaction.
Data from Purchased table:
Customer_ID Last_Name First_name Colour Car SUV Truck 4WD
50 Smith John Black Yes
50 Smith John Red Yes
50 Smith John Red Yes Yes
50 Smith John Red Yes
20 McGregor Katie Blue Yes Yes
20 McGregor Katie Red Yes
20 McGregor Katie Black Yes
20 McGregor Katie Red Yes Yes
11 Yang Karen Red Yes
11 Yang Karen Red Yes
90 Wilkins Melissa Black Yes
90 Wilkins Melissa Red Yes Yes
90 Wilkins Melissa Blue Yes Yes
90 Wilkins Melissa Grey
135 Barnes Tom Red Yes Yes Yes
135 Barnes Tom Blue Yes
135 Barnes Tom Black Yes
output result for any red colour car:
Customer_ID Last_Name First_name Car SUV Truck 4WD 50 Smith John Yes No No Yes 20 McGregor Katie Yes No No No 11 Yang Karen Yes Yes No No 90 Wilkins Melissa No No No Yes 135 Barnes Tom Yes No Yes Yes
Thank you for your help
currently I am using below method statements;
WITH
sorted_ as (
select
distinct (colour) ,
customer_id ,
last_name ,
car ,
SUV ,
truck ,
4wd
from purchased)
select
e. customer_id ,
e. last_name ,
nvl (e. car , 'No') car ,
nvl (e. SUV , 'No') SUV ,
nvl (e. truck , 'No') truck ,
nvl (e. 4wd , 'No') 4wd
from (
select
a. customer_id ,
a. last_name ,
a. car ,
b. SUV ,
c. truck ,
d. 4wd
from (
select
distinct(car),
customer_id
from sorted_
where colour = 'Red'
and car = 'Yes') a
left join (
select
distinct(SUV),
customer_id
from sorted_
where colour = 'Red'
and SUV = 'Yes') b
ON a. customer_id = b. customer_id
left join (
select
distinct(truck),
customer_id
from sorted_
where colour = 'Red'
and truck = 'Yes') c
ON a. customer_id = c. customer_id
left join (
select
distinct(4wd),
customer_id
from sorted_
where colour = 'Red'
and 4wd = 'Yes') d
ON a. customer_id = d. customer_id
) e
CodePudding user response：
I think I understood what you want, but desired output (screenshot you posted) doesn't match. For example, McGregor has two "Red" rows with car and 4WD marked as such, while your output says that "Yes" is set to car only. Why? I can't run query you posted as it is invalid and - thus - returns nothing at all.
Sample data:
SQL> select * From purchased;
CUSTOMER_ID LAST_NAME FIRST_NAME COLOU CAR SUV TRUCK FOURWD
----------- ---------- ---------- ----- --- --- ----- -------
50 Smith John Black Yes
50 Smith John Red Yes
50 Smith John Red Yes Yes
50 Smith John Red Yes
20 McGregor Katie Blue Yes Yes
20 McGregor Katie Red Yes --> car is red
20 McGregor Katie Black Yes
20 McGregor Katie Red Yes Yes --> car and 4WD are red
11 Yang Karen Red Yes
11 Yang Karen Red Yes
90 Wilkins Melissa Black Yes
90 Wilkins Melissa Red Yes Yes
90 Wilkins Melissa Blue Yes Yes
90 Wilkins Melissa Grey
135 Barnes Tom Red Yes Yes Yes
135 Barnes Tom Blue Yes
135 Barnes Tom Black Yes
17 rows selected.
Query, as I understood it:
SQL> select customer_id, last_name, first_name,
2 max(case when colour = 'Red' then nvl(car, 'No') end) as car,
3 max(case when colour = 'Red' then nvl(suv, 'No') end) as suv,
4 max(case when colour = 'Red' then nvl(truck, 'No') end) as truck,
5 max(case when colour = 'Red' then nvl(fourwd, 'No') end) as fourwd
6 From purchased
7 where colour = 'Red'
8 group by customer_id, last_name, first_name
9 /
CUSTOMER_ID LAST_NAME FIRST_NAME CAR SUV TRUCK FOURWD
----------- ---------- ---------- --- --- ----- -------
50 Smith John Yes No Yes Yes
20 McGregor Katie Yes No No Yes
11 Yang Karen Yes Yes No No
90 Wilkins Melissa No No Yes Yes
135 Barnes Tom Yes No Yes Yes
SQL>