Hello Friends! บทความนี้มาเรียนวิธีการเขียน Aggregate Functions (count, sum) ร่วมกับ CASE statement เพื่อนับหรือหาผลรวมของคอลัมน์แบบมีเงื่อนไข เขียนง่าย และมีโอกาสได้ใช้บ่อยเลยในชีวิตจริง
- Aggregate Functions คือฟังก์ชันคำนวณค่าสถิติง่ายๆของ SQL ตัวหลักๆที่เราใช้บ่อยๆมี 5 ตัวคือ AVG, SUM, MIN, MAX และ COUNT
- CASE คือการเขียนเงื่อนไขเหมือนฟังก์ชัน IF/ IFS ของ Excel
Tip – เราเขียน CASE เพื่อสร้างคอลัมน์ใหม่ เช่น segments ลูกค้าเป็น high, medium, low values และใช้ SUM หรือ COUNT เพื่อหาผลรวมหรือนับจำนวน segments ในคอลัมน์นั้น
Example Table
สำหรับตัวอย่างในบทความนี้ เราใช้ customers table ใน chinook.db เพื่อนๆสามารถดาวน์โหลดไฟล์ database ได้ที่นี่

บทความนี้มี 7 ตัวอย่างการใช้งานจริงของ Aggregate Functions + CASE
- วิธีการเขียน CASE เบื้องต้น
- SUM CASE
- SUM CASE 1/0 (ได้ผลลัพธ์เหมือนกับการ COUNT CASE)
- AVG CASE 1/0 (หา % ของ CASE ที่เราต้องการ)
- COUNT CASE
- Another Example
- Pivot Table (long – wide format)
Basic CASE
CASE ใช้สำหรับสร้างคอลัมน์ใหม่ (derived column) แบบมีเงื่อนไข ด้านล่างคือวิธีการเขียน CASE เบื้องต้น
- ขึ้นต้นด้วย CASE ปิดท้ายด้วย END
- WHEN ถ้าเงื่อนไขนี้เป็นจริง .. THEN แสดงค่านี้ ..
- ELSE แสดงค่านี้สำหรับเงื่อนไขอื่นๆ i.e. all other conditions
- เราสามารถตั้งชื่อคอลัมน์ใหม่ได้หลัง END
CASE
WHEN ... THEN ...
WHEN ... THEN ...
ELSE ...
END AS new_column
ถ้าดูที่คอลัมน์ company ใน customers table จะเห็นว่าลูกค้าบางคนจะขึ้นเป็นค่า NULL ถ้ามองในมุม business logic เราอาจ assume ได้ว่า
- ลูกค้าที่ company IS NULL เป็นลูกค้าแบบ B2C i.e. end customers
- ลูกค้าที่ company IS NOT NULL เป็นลูกค้าแบบ B2B i.e. corporate

เราสามารถสร้าง result set นี้ได้ง่ายด้วย query นี้ แอดเขียน CASE WHEN สองเงื่อนไข ข้อแรกสำหรับลูกค้า end customer และข้อสองสำหรับลูกค้า corporate
Tip – การเขียนเงื่อนไขใน WHEN เหมือนกับที่เราเขียนใน WHERE clause
-- basic case statement
SELECT
company,
CASE
WHEN company IS NULL THEN 'end_customer'
WHEN company IS NOT NULL THEN 'corporate'
END AS segment
FROM customers;
หรือเขียน ELSE แทนเงื่อนไขข้อที่สองแบบนี้ (ELSE = all else or other conditions)
-- we can use ELSE to achieve the same result
SELECT
company,
CASE
WHEN company IS NULL THEN 'end_customer'
ELSE 'corporate'
END AS segment
FROM customers;
SUM CASE
มาลองดู basic query ง่ายๆตัวแรกกันก่อน query นี้เราต้องการหาผลรวมของ total invoice ในปี 2009 ทั้งสอง queries ด้านล่างได้ผลลัพธ์เหมือนกัน
- SUM + CASE หาผลรวมของ total invoice ในปี 2009
- SUM + WHERE หาผลรวมของคอลัมน์ total invoice กรองเฉพาะ transactions ของปี 2009
-- sum total invoice in 2009
SELECT
SUM(CASE WHEN strftime('%Y', invoicedate) = '2009'
THEN total END) total_invoice_2009
FROM invoices;
-- another way to write this query
SELECT SUM(total) total_invoice_2009
FROM invoices
WHERE strftime('%Y', invoicedate) = '2009';
SUM CASE 1/0
ตัวอย่างนี้เป็นทริคที่แอดใช้บ่อยๆเพื่อนับจำนวน rows ที่ตรงกับเงื่อนไขที่เราต้องการ ด้วยการประยุกต์ใช้ SUM CASE 1/0 (hint – query นี้เทียบเท่ากับการเขียน COUNT(*) ในหัวข้อถัดไป) โดย logic ของเลข 1/0 คือ
- 1 = เงื่อนไขเป็นจริง (TRUE) 0 = เงื่อนไขไม่เป็นจริง (FALSE)
- SUM( 1, 0, 0, 1, 1, … ) คือการนับจำนวน rows ทั้งหมดที่ตรงกับเงื่อนไขของเรา

-- if row meets condition then 1 else 0
SELECT
SUM(CASE WHEN company IS NULL THEN 1 ELSE 0 END) end_customer,
SUM(CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END) corporate
FROM customers;
Tip – จริงๆ ELSE 0 ในตัวอย่างนี้เป็น optional ไม่ต้องเขียนก็ได้ 😝
AVG CASE 1/0
ถ้าเราเปลี่ยนฟังก์ชัน SUM เป็น AVG จะเป็นการหาค่าเฉลี่ยของคอลัมน์ 1/0 เทียบเท่ากับการคำนวณ % ของเงื่อนไขที่เราต้องการ เช่น % ของลูกค้า end customer vs. corporate
Query นี้แอดใช้ ROUND( ) เพื่อปรับจำนวนทศนิยมเป็นสองตำแหน่ง end customer = .83 และ corporate = .17 รวมกันเป็น 1.0 หรือ 100% พอดี
-- find % of end customer and corporate
SELECT
ROUND(AVG(CASE WHEN company IS NULL THEN 1 ELSE 0 END), 2) end_customer,
ROUND(AVG(CASE WHEN company IS NOT NULL THEN 1 ELSE 0 END), 2) corporate
FROM customers;
COUNT CASE
การเขียน SUM(CASE 1/0) ในตัวอย่างด้านบนเทียบเท่ากับการเขียน COUNT(*) + GROUP BY โดย query ด้านล่าง แอดเขียน CASE เพื่อสร้างคอลัมน์ segment และนับจำนวนลูกค้าแต่ละ segment ง่ายๆ

-- use count case to achieve the same result as sum case 1/0
SELECT
CASE WHEN company IS NOT NULL THEN 'corporate'
ELSE 'end_customer'
END AS segment,
COUNT(*) n
FROM customers
GROUP BY 1;
Another Example
มาลองดูอีกตัวอย่าง การเขียน SUM + CASE แบบมากกว่าหนึ่งเงื่อนไข ตัวอย่าง query นี้ แอดนับจำนวนลูกค้าในประเทศอเมริกาที่เป็นลูกค้า corporate (B2B customers = company IS NOT NULL)

โดยทั่วไป เราสามารถเขียน SQL queries ได้หลายแบบเพื่อให้ได้คำตอบแบบเดียวกัน
- SUM + CASE เพื่อหาผลรวม (1/0) จำนวน usa_corp
- COUNT + WHERE เพื่อนับจำนวน records ที่ตรงกับเงื่อนไขของเรา
ลองรันสอง queries ด้านล่างจะได้จำนวนลูกค้า usa_corp = 3 เท่ากัน จะใช้ SUM(1/0) หรือ COUNT(*) ก็ได้ผลลัพธ์เหมือนกันในกรณีนี้
-- count USA corporate in the table
SELECT
SUM(CASE WHEN company IS NOT NULL
AND country = 'USA' THEN 1 ELSE 0 END) AS 'usa_corp'
FROM customers;
-- this query will do the same
SELECT COUNT(*) usa_corp
FROM customers
WHERE company IS NOT NULL AND country = 'USA';
Pivot Table
ตัวอย่างสุดท้ายมาดูวิธีการเขียน SUM + CASE เพื่อเปลี่ยนหน้าตาข้อมูลแบบ long เป็น wide format
- Long format เหมาะสมสำหรับการวิเคราะห์ข้อมูล (raw data)
- Wide format เหมาะสมสำหรับการทำรายงาน สรุปผลสถิติเบื้องต้น (report)

Query นี้เราหาผลรวม SUM(jazz_revenue) แบ่งตามปี และจับกลุ่มผลรวมนี้ด้วยคอลัมน์ fullname ใน GROUP BY clause; assume ว่าเรามีตาราง long_format อยู่ใน database ของเราแล้ว
-- pivot long to wide format for reporting
SELECT
fullname,
SUM(CASE WHEN year = '2009' THEN jazz_revenue END) '2009',
SUM(CASE WHEN year = '2010' THEN jazz_revenue END) '2010',
SUM(CASE WHEN year = '2011' THEN jazz_revenue END) '2011',
SUM(CASE WHEN year = '2012' THEN jazz_revenue END) '2012',
SUM(CASE WHEN year = '2013' THEN jazz_revenue END) '2013',
SUM(jazz_revenue) total_sales
FROM long_format
GROUP BY 1 ORDER BY total_sales DESC;
อ่านบทความล่าสุดของเราเรื่อง long-wide data transformation ได้ที่ลิ้งด้านล่าง แอดสอนทำใน R, Excel (Pivot Table) และ SQL ตามลำดับ
Summary
บทความนี้ เราได้เรียนวิธีการเขียน Aggregate Functions + CASE หลายๆแบบ โดยตัวหลักๆที่เราใช้กันเวลาวิเคราะห์ข้อมูลคือ SUM + CASE และ COUNT + CASE
- SUM + CASE หาผลรวมปกติ
- หรือ SUM + CASE 1/0 เพื่อนับจำนวน rows ที่ตรงกับเงื่อนไข เทียบเท่ากับการเขียน COUNT + CASE
- หรือ AVG + CASE 1/0 เพื่อหาค่าร้อยละ (%) ของ case ที่เราเขียน
- ในชีวิตจริง อย่าลืมทำความสะอาดค่า NULL ก่อนใช้ Aggregate Functions ด้วยนะคร้าบ
Leave a Reply