Using CASE and WHEN

Private: Mastering SQL using Postgresql Predefined Functions Using CASE and WHEN

At times we might have to select values from multiple columns conditionally.

We can use CASE and WHEN for that.
Let us implement this conditional logic to come up with derived order_status.
- If order_status is COMPLETE or CLOSED, set COMPLETED
- If order_status have PENDING in it, then we will say PENDING
- If order_status have PROCESSING or PAYMENT_REVIEW in it, then we will say PENDING
- We will set all others as OTHER
We can also have ELSE as part of CASE and WHEN.

%load_ext sql

The sql extension is already loaded. To reload it, use:
  %reload_ext sql

%env DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

env: DATABASE_URL=postgresql://itversity_retail_user:retail_password@localhost:5432/itversity_retail_db

%sql DROP TABLE IF EXISTS sales

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

CREATE TABLE IF NOT EXISTS sales(
    sales_person_id INT,
    sales_amount FLOAT,
    commission_pct INT
)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
Done.

[]

%%sql

INSERT INTO sales VALUES
    (1, 1000, 10),
    (2, 1500, 8),
    (3, 500, NULL),
    (4, 800, 5),
    (5, 250, NULL)

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.

[]

%%sql

SELECT * FROM sales

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.

sales_person_id	sales_amount	commission_pct
1	1000.0	10
2	1500.0	8
3	500.0	None
4	800.0	5
5	250.0	None

%%sql

SELECT s.*,
    CASE WHEN commission_pct IS NOT NULL 
        THEN round((sales_amount * commission_pct / 100)::numeric, 2)
    ELSE 0
    END AS commission_amount
FROM sales s

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
5 rows affected.

sales_person_id	sales_amount	commission_pct	commission_amount
1	1000.0	10	100.00
2	1500.0	8	120.00
3	500.0	None	0
4	800.0	5	40.00
5	250.0	None	0

%%sql

SELECT DISTINCT order_status FROM orders
ORDER BY order_status

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
9 rows affected.

order_status
CANCELED
CLOSED
COMPLETE
ON_HOLD
PAYMENT_REVIEW
PENDING
PENDING_PAYMENT
PROCESSING
SUSPECTED_FRAUD

%%sql

SELECT o.*,
    CASE WHEN order_status IN ('COMPLETE', 'CLOSED') THEN 'COMPLETED'
    END AS updated_order_status
FROM orders o
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_id	order_date	order_customer_id	order_status	updated_order_status
1021	2013-07-30 00:00:00	10118	COMPLETE	COMPLETED
4068	2013-08-17 00:00:00	12293	PENDING	None
5881	2013-08-30 00:00:00	3715	CLOSED	COMPLETED
7564	2013-09-09 00:00:00	8648	CLOSED	COMPLETED
8766	2013-09-18 00:00:00	855	COMPLETE	COMPLETED
8926	2013-09-19 00:00:00	10517	ON_HOLD	None
9290	2013-09-21 00:00:00	11879	COMPLETE	COMPLETED
9793	2013-09-24 00:00:00	9809	COMPLETE	COMPLETED
9816	2013-09-24 00:00:00	1753	COMPLETE	COMPLETED
14047	2013-10-20 00:00:00	6473	CLOSED	COMPLETED

%%sql

SELECT o.*,
    CASE WHEN order_status IN ('COMPLETE', 'CLOSED') THEN 'COMPLETED'
    ELSE order_status
    END AS updated_order_status
FROM orders o
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_id	order_date	order_customer_id	order_status	updated_order_status
1021	2013-07-30 00:00:00	10118	COMPLETE	COMPLETED
4068	2013-08-17 00:00:00	12293	PENDING	PENDING
5881	2013-08-30 00:00:00	3715	CLOSED	COMPLETED
7564	2013-09-09 00:00:00	8648	CLOSED	COMPLETED
8766	2013-09-18 00:00:00	855	COMPLETE	COMPLETED
8926	2013-09-19 00:00:00	10517	ON_HOLD	ON_HOLD
9290	2013-09-21 00:00:00	11879	COMPLETE	COMPLETED
9793	2013-09-24 00:00:00	9809	COMPLETE	COMPLETED
9816	2013-09-24 00:00:00	1753	COMPLETE	COMPLETED
14047	2013-10-20 00:00:00	6473	CLOSED	COMPLETED

%%sql

SELECT o.*,
    CASE 
        WHEN order_status IN ('COMPLETE', 'CLOSED') THEN 'COMPLETED'
        WHEN order_status ~ 'PENDING' THEN 'PENDING'
        ELSE 'OTHER'
    END AS updated_order_status
FROM orders o
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_id	order_date	order_customer_id	order_status	updated_order_status
1021	2013-07-30 00:00:00	10118	COMPLETE	COMPLETED
4068	2013-08-17 00:00:00	12293	PENDING	PENDING
5881	2013-08-30 00:00:00	3715	CLOSED	COMPLETED
7564	2013-09-09 00:00:00	8648	CLOSED	COMPLETED
8766	2013-09-18 00:00:00	855	COMPLETE	COMPLETED
8926	2013-09-19 00:00:00	10517	ON_HOLD	OTHER
9290	2013-09-21 00:00:00	11879	COMPLETE	COMPLETED
9793	2013-09-24 00:00:00	9809	COMPLETE	COMPLETED
9816	2013-09-24 00:00:00	1753	COMPLETE	COMPLETED
14047	2013-10-20 00:00:00	6473	CLOSED	COMPLETED

%%sql

SELECT o.*,
    CASE 
        WHEN order_status IN ('COMPLETE', 'CLOSED') THEN 'COMPLETED'
        WHEN order_status LIKE '%PENDING%' OR order_status IN ('PROCESSING', 'PAYMENT_REVIEW')
            THEN 'PENDING'
        ELSE 'OTHER'
    END AS updated_order_status
FROM orders o
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
10 rows affected.

order_id	order_date	order_customer_id	order_status	updated_order_status
1021	2013-07-30 00:00:00	10118	COMPLETE	COMPLETED
4068	2013-08-17 00:00:00	12293	PENDING	PENDING
5881	2013-08-30 00:00:00	3715	CLOSED	COMPLETED
7564	2013-09-09 00:00:00	8648	CLOSED	COMPLETED
8766	2013-09-18 00:00:00	855	COMPLETE	COMPLETED
8926	2013-09-19 00:00:00	10517	ON_HOLD	OTHER
9290	2013-09-21 00:00:00	11879	COMPLETE	COMPLETED
9793	2013-09-24 00:00:00	9809	COMPLETE	COMPLETED
9816	2013-09-24 00:00:00	1753	COMPLETE	COMPLETED
14047	2013-10-20 00:00:00	6473	CLOSED	COMPLETED

%%sql

SELECT DISTINCT order_status,
    CASE 
        WHEN order_status IN ('COMPLETE', 'CLOSED') THEN 'COMPLETED'
        WHEN order_status LIKE '%PENDING%' OR order_status IN ('PROCESSING', 'PAYMENT_REVIEW')
            THEN 'PENDING'
        ELSE 'OTHER'
    END AS updated_order_status
FROM orders
ORDER BY updated_order_status
LIMIT 10

 * postgresql://itversity_retail_user:***@localhost:5432/itversity_retail_db
9 rows affected.

order_status	updated_order_status
CLOSED	COMPLETED
COMPLETE	COMPLETED
SUSPECTED_FRAUD	OTHER
CANCELED	OTHER
ON_HOLD	OTHER
PAYMENT_REVIEW	PENDING
PENDING_PAYMENT	PENDING
PROCESSING	PENDING
PENDING	PENDING

Previous Topic

Back to Lesson

Next Topic