MySQL ออกรายงานข้อมูลรายเดือน

เทคนิคการสรุปข้อมูลด้วย MySql ที่เก็บข้อมูลเป็นรายครั้ง เช่น รายการ order ของสินค้า ตามรูป ความต้องการคือ ทำสรุปข้อมูลการขายเป็นรายเดือน แยกตามปี ซึ่งจริงๆแล้ว เราจะ Query ข้อมูลออกมาทั้งหมดแล้วจัดกลุ่มด้วย PHP ก็ได้ แต่ก็มีวิธีที่ง่ายกว่า โดยใช้คำสั่งของ MySQL โดยตรงในการสรุปข้อมูล ซึ่งสามารถให้ผลลัพท์ในแบบที่ต้องการได้แทบจะทันที

SELECT

YEAR(`last_update`) AS `year`,

SUM(IF(MONTH(`last_update`)=1,`total`,NULL)) AS `1`,

SUM(IF(MONTH(`last_update`)=2,`total`,NULL)) AS `2`,

SUM(IF(MONTH(`last_update`)=3,`total`,NULL)) AS `3`,

SUM(IF(MONTH(`last_update`)=4,`total`,NULL)) AS `4`,

SUM(IF(MONTH(`last_update`)=5,`total`,NULL)) AS `5`,

SUM(IF(MONTH(`last_update`)=6,`total`,NULL)) AS `6`,

SUM(IF(MONTH(`last_update`)=7,`total`,NULL)) AS `7`,

SUM(IF(MONTH(`last_update`)=8,`total`,NULL)) AS `8`,

SUM(IF(MONTH(`last_update`)=9,`total`,NULL)) AS `9`,

SUM(IF(MONTH(`last_update`)=10,`total`,NULL)) AS `10`,

SUM(IF(MONTH(`last_update`)=11,`total`,NULL)) AS `11`,

SUM(IF(MONTH(`last_update`)=12,`total`,NULL)) AS `12`

FROM `orders`

GROUP BY `year`

ผลลัพท์ที่ได้ พระเอกของเราคือคำสั่ง IF
IF(condition, value_true, value_false)
ซึ่งเป็นฟังก์ชั่นที่ลดรูปมาจาก คำสั่ง CASE WHEN ของ SQL
CASE WHEN condition THEN value_true ELSE value_false END
ถ้า condition เป็นจริง จะคืนค่า value_true ถ้าไม่จริง จะคืนค่า value_false (ทั้งสองวิธีสามารถใช้แทนกันได้นะครับ เนื่องจาก MySQL รุ่นเก่าๆ อาจไม่มีคำสั่ง IF)

หลักการก็คือ
SUM( IF( MONTH(`last_update`) = 1, `total`, NULL ) ) AS `1`
ถ้าเดือนเท่ากับ 1 คืนค่า total ของรายการนั้นๆ เอาไปไปบวกกัน (SUM) แล้วคืนค่าไปที่ คอลัมน์ 1 ถ้าเดือนไม่เท่ากับ 1 จะคืนค่า NULL ซึ่งคำสั่ง SUM ก็จะไม่นำไปรวมเป็นผลลัพท์ ซึ่งเดือนอื่นๆก็ทำเช่นเดียวกัน ก็จะได้คอลัมน์มาทั้งหมด 12 คอลัมน์ตามรายเดือน

คำเตือน 1 ต้องอยู่ภายใต้ `` นะครับ MySQL ถึงจะมองเป็นชื่อคอลัมน์

อีกสักตัวอย่าง คล้ายๆกันเป็นการนับจำนวนการทำรายการรายเดือน

SELECT

YEAR(`last_update`) AS `year`,

SUM(IF(MONTH(`last_update`)=1,1,0)) AS `1`,

SUM(IF(MONTH(`last_update`)=2,1,0)) AS `2`,

SUM(IF(MONTH(`last_update`)=3,1,0)) AS `3`,

SUM(IF(MONTH(`last_update`)=4,1,0)) AS `4`,

SUM(IF(MONTH(`last_update`)=5,1,0)) AS `5`,

SUM(IF(MONTH(`last_update`)=6,1,0)) AS `6`,

SUM(IF(MONTH(`last_update`)=7,1,0)) AS `7`,

SUM(IF(MONTH(`last_update`)=8,1,0)) AS `8`,

SUM(IF(MONTH(`last_update`)=9,1,0)) AS `9`,

SUM(IF(MONTH(`last_update`)=10,1,0)) AS `10`,

SUM(IF(MONTH(`last_update`)=11,1,0)) AS `11`,

SUM(IF(MONTH(`last_update`)=12,1,0)) AS `12`

FROM `orders`

GROUP BY `year`

ผลลัพท์ที่ได้