นับจำนวนวันและ Sum ต่อวันใน MySQL ด้วย GROUP BY DATE()

เมื่อมีข้อมูล DATETIME และค่าตัวเลข (Count) ต้องการรวมค่า Count ต่อวัน ให้ใช้ DATE() แยกส่วนวันที่จาก DATETIME แล้ว GROUP BY และ SUM()

สร้างตารางและข้อมูล

mysql> create table DemoTable (
    ShippingDate datetime,
    CountOfDate int
);

mysql> insert into DemoTable values
('2018-01-31',6),('2016-12-01',15),('2016-12-01',10),
('2016-12-01',5),('2019-04-09',3),('2018-01-31',4),('2019-04-09',4);

ข้อมูลดิบ (7 แถว, 3 วันที่)

+---------------------+-------------+
| ShippingDate        | CountOfDate |
+---------------------+-------------+
| 2018-01-31 00:00:00 | 6           |
| 2016-12-01 00:00:00 | 15          |
| 2016-12-01 00:00:00 | 10          |
| 2016-12-01 00:00:00 | 5           |
| 2019-04-09 00:00:00 | 3           |
| 2018-01-31 00:00:00 | 4           |
| 2019-04-09 00:00:00 | 4           |
+---------------------+-------------+

คำสั่ง GROUP BY DATE แล้ว SUM

mysql> select date(ShippingDate), sum(CountOfDate) 
from DemoTable group by date(ShippingDate);

ผลลัพธ์: รวมต่อวัน

+--------------------+------------------+
| date(ShippingDate) | sum(CountOfDate) |
+--------------------+------------------+
| 2018-01-31         | 10               |
| 2016-12-01         | 30               |
| 2019-04-09         | 7                |
+--------------------+------------------+

คำอธิบาย

• DATE(ShippingDate) - แปลง DATETIME → DATE (ตัด Time ออก)
• GROUP BY DATE(ShippingDate) - จัดกลุ่มตามวันที่
• SUM(CountOfDate) - รวมค่าในแต่ละกลุ่ม

ขยาย: เพิ่ม Aggregate อื่น

SELECT 
    DATE(ShippingDate) AS ship_date,
    SUM(CountOfDate) AS total_count,
    COUNT(*) AS record_count,
    AVG(CountOfDate) AS avg_count,
    MIN(CountOfDate) AS min_count,
    MAX(CountOfDate) AS max_count
FROM DemoTable
GROUP BY DATE(ShippingDate)
ORDER BY ship_date;

ประสิทธิภาพ: Index และ Functional Index

• GROUP BY DATE(col) - ไม่ใช้ Index ธรรมดา (Function on Column)
• MySQL 8.0+: สร้าง Functional Index

ALTER TABLE DemoTable 
ADD INDEX idx_ship_date_only ((DATE(ShippingDate)));

Timezone Consideration

• DATE() ใช้ Timezone ของ Session
• ข้อมูลเก็บ UTC, Session เป็น +07:00 → DATE() จะเป็นวันถัดไปสำหรับเวลา 17:00-23:59 UTC
• แนะนำ: เก็บ UTC, Query ด้วย UTC, แปลง Timezone แค่ตอนแสดงผล

Window Function (MySQL 8.0+) - Running Total ต่อวัน

SELECT 
    DATE(ShippingDate) AS ship_date,
    SUM(CountOfDate) AS daily_total,
    SUM(SUM(CountOfDate)) OVER (ORDER BY DATE(ShippingDate)) AS running_total
FROM DemoTable
GROUP BY DATE(ShippingDate);