SQL CASE in SELECT

The most common place CASE appears — creating an entirely new, derived column that doesn't exist anywhere in the actual table.

What & Why

Every example so far in this series has already been doing this — but it's worth calling out explicitly: CASE inside SELECT is how a query manufactures a brand-new column from existing data. The table itself never had a "spend_flag" column; the query creates it on the fly, purely from the logic inside the CASE expression.

See How It Works

BUSINESS QUESTION

Marketing wants campaign status converted into an analyst-friendly delivery label.

id	name	channel	spend	start_date	end_date	status	target_segment
1	Spring Launch	google_ads	55000.00	2024-01-15	2024-03-31	active	smb
2	Retention Webinar	email	45000.00	2024-02-10	2024-04-15	active	enterprise
3	Finance Retargeting	linkedin	50000.00	2024-03-12	2024-05-31	active	enterprise
4	Enterprise Search	google_ads	60000.00	2024-04-01	2024-06-30	active	enterprise

EXAMPLE QUERY

SELECT
  name,
  status,
  CASE WHEN status = 'active' THEN 'in market' ELSE 'not running' END AS delivery_state
FROM marketing.campaigns
ORDER BY name;

RESULT — exact output from the displayed Queryflo rows

name	status	delivery_state
Enterprise Search	active	in market
Finance Retargeting	active	in market
Retention Webinar	active	in market
Spring Launch	active	in market

CASE adds a derived delivery_state without changing marketing.campaigns.

Now You Try

Practice this concept

Marketing wants campaign status converted into an analyst-friendly delivery label.

Available schema

marketing

Prefix tables with marketing.table_name.

namestatusdelivery_state

marketing.campaigns

Column	Type
id	integer
name	text
channel	text
spend	numeric
start_date	date
end_date	date
status	text
target_segment	text
legacy_id	text

query.sql

Intermediate business practice