วิธีสร้างคอลัมน์เดือนจากวันที่ และรวมยอด (Sum) ตามเดือนที่มีวันที่ซ้ำใน MySQL

เมื่อต้องการจัดกลุ่มข้อมูลตามเดือนจากคอลัมน์วันที่ (Date) และคำนวณผลรวม (Sum) ของคอลัมน์ตัวเลข โดยเฉพาะกรณีที่มีวันที่ซ้ำกันในเดือนเดียวกัน สามารถใช้ฟังก์ชัน DATE_FORMAT() ร่วมกับ GROUP BY ได้อย่างมีประสิทธิภาพ

ตัวอย่างการใช้งาน

1. สร้างตารางตัวอย่าง

mysql> CREATE TABLE DemoTable (
    -> PurchaseDate DATE,
    -> Amount INT
    -> );
Query OK, 0 rows affected (0.52 sec)

2. เพิ่มข้อมูลทดสอบ (มีวันที่ซ้ำในเดือนตุลาคม 2018)

mysql> INSERT INTO DemoTable VALUES('2019-10-12',500);
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO DemoTable VALUES('2018-10-12',1000);
Query OK, 1 row affected (0.17 sec)
mysql> INSERT INTO DemoTable VALUES('2019-01-10',600);
Query OK, 1 row affected (0.12 sec)
mysql> INSERT INTO DemoTable VALUES('2018-10-12',600);
Query OK, 1 row affected (0.15 sec)
mysql> INSERT INTO DemoTable VALUES('2018-11-10',800);
Query OK, 1 row affected (0.18 sec)

3. แสดงข้อมูลทั้งหมด

mysql> SELECT * FROM DemoTable;

+--------------+--------+
| PurchaseDate | Amount |
+--------------+--------+
| 2019-10-12   |    500 |
| 2018-10-12   |   1000 |
| 2019-01-10   |    600 |
| 2018-10-12   |    600 |
| 2018-11-10   |    800 |
+--------------+--------+
5 rows in set (0.00 sec)

4. Query รวมยอดตามเดือน (จัดกลุ่มตาม ปี-เดือน แล้วแสดงแค่ชื่อเดือน)

mysql> SELECT SUM(Amount) AS Amount, DATE_FORMAT(PurchaseDate,'%b') AS Month
    -> FROM DemoTable
    -> GROUP BY DATE_FORMAT(PurchaseDate,'%Y-%m');

+--------+-------+
| Amount | Month |
+--------+-------+
|    500 | Oct   |
|   1600 | Oct   |
|    600 | Jan   |
|    800 | Nov   |
+--------+-------+
4 rows in set (0.00 sec)

คำอธิบายสำคัญ

• DATE_FORMAT(PurchaseDate,'%Y-%m') ใช้ใน GROUP BY เพื่อจัดกลุ่มที่ถูกต้อง (แยกปีด้วย) ไม่ให้เดือนตุลาคม ปี 2018 และ 2019 รวมกัน
• DATE_FORMAT(PurchaseDate,'%b') ใช้ใน SELECT เพื่อแสดงชื่อเดือนย่อ (Jan, Feb, Mar...)
• SUM(Amount) จะรวมยอดเฉพาะในกลุ่มเดือนนั้นๆ (如เดือนตุลาคม 2018 มี 2 เรคคอร์ด รวมเป็น 1600)

รูปแบบวันที่อื่นๆ ที่ใช้บ่อย