CODEWITHSUNDEEP
sqloracle-databaseselectgroup-byhaving-clause
Katya
Katya

Reputation: 1

Trouble with SQL request

I just want to output only those records, that for the same name customer_name have cust_valid= 'I' and cust_valid='A'

I tried to do this, but the rezult for cust_valid have only records 'A' enter code here

SELECT c.cust_first_name ||' '|| c.cust_last_name AS CUSTOMER_NAME, 
            to_number(SUBSTR(c.cust_income_level, INSTR(c.cust_income_level, '-')+2), '999999') as UPPER_INCOME_LEVEL, 
            sum(s.amount_sold) as TOTAL_AMOUNT, 
            (CASE  WHEN c.cust_credit_limit <= 1500 THEN 'Low_limit'
             ELSE 'High_limit'
             END) credit_limit_level,
             c.cust_valid
FROM SH.customers c  
JOIN sh.sales s on c.cust_id = s.cust_id
WHERE c.cust_valid = 'A' AND c.cust_income_level like '%-%' 
GROUP BY c.cust_first_name, c.cust_last_name, c.cust_income_level, c.cust_credit_limit, c.cust_valid 
HAVING SUM(s.amount_sold) > (c.cust_credit_limit * 50)
ORDER BY UPPER_INCOME_LEVEL DESC, CUSTOMER_NAME;

Upvotes: 0

Views: 49

Answers (3)

Katya
Katya

Reputation: 1

I tried, but no result. I dont think I showed the condition correct.

I have to explain... Modify the query to display the name of the client (CUSTOMER_NAME) for which there are rows with CUST_VALID = ‘A’ and rows with CUST_VALID = ‘I’ as a separate result.

Upvotes: 0

Popeye
Popeye

Reputation: 35900

You need to use the EXISTS clause in WHERE condition as follows:

and exists (select 1
              from sh.customers cin
             where cin.cust_id = c.cust_id
               and c.c.cust_valid = 'I'
           )

Upvotes: 1

GMB
GMB

Reputation: 222432

If I followed you correctly, you can implement this filtering by modifying your having clause:

HAVING 
    SUM(s.amount_sold) > (c.cust_credit_limit * 50)
    AND MAX(CASE WHEN cust_valid= 'I' THEN 1 ELSE 0 END) = 1
    AND MAX(CASE WHEN cust_valid= 'A' THEN 1 ELSE 0 END) = 1

and cust_valid='A'

Upvotes: 0

Related Questions