How to show out null value using NVL Oracle SQL?-CodePudding

I want to select out a list of customer with their total purchases amount made.

The sum of the purchase_amount must not include the 'void' and 'failed' status amount

Currently, I have the sql script as below but the customer who make the 'failed' and 'void' status didn't show out.

Which part am I doing wrong?

Script:

SELECT C.CUSTOMER_ID, C.NAME, NVL(SUM(AMOUNT), 0) AS PURCHASE_AMOUNT
FROM CUSTOMER C
LEFT JOIN TRANSACTION T ON T.CUSTOMER_ID = C.CUSTOMER_ID
WHERE T.STATUS NOT IN('Void', 'Failed')
GROUP BY C.CUSTOMER_ID, C.NAME;

Actual Result:

Expected Result:

CodePudding user response：

The presence of the condition T.STATUS NOT IN('Void', 'Failed') in the WHERE clause effectively turns the LEFT JOIN of the TRANSACTION table into an inner join, because no row can be accepted if the condition in the WHERE clause is not satisfied. To have the T.STATUS... condition treated as part of the LEFT JOIN it must appear in the ON clause of the join itself:

SELECT C.CUSTOMER_ID, C.NAME, NVL(SUM(AMOUNT), 0) AS PURCHASE_AMOUNT
FROM CUSTOMER C
LEFT JOIN TRANSACTION T
  ON T.CUSTOMER_ID = C.CUSTOMER_ID AND
     T.STATUS NOT IN('Void', 'Failed')
GROUP BY C.CUSTOMER_ID, C.NAME;

CodePudding user response：

You can check 'T.STATUS' with CASE in select part instead of checking it in where clause.

SELECT
    c.customer_id,
    c.name,
    SUM(
        CASE
            WHEN t.status NOT IN('Void', 'Failed') THEN
                amount
            ELSE
                0
        END
    ) AS purchase_amount
FROM
    customer    c
    LEFT JOIN transaction t ON t.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.name;