mysql - 获取 Chart.js 一年间隔内每个月的 SUM 数据

Question

我有一个包含 IP 会计数据的表，

我想获取从当天算起一年内每个月(一月、二月、三月...)的SUM(bytes)

column: bytes <-- bytes

column: timeanddate <-- timestamp

我的查询:

SELECT (SELECT SUM(bytes) FROM ip_accounting);

请帮忙。

Answer 1

我已经找到了我正在寻找的答案

SELECT 
    SUM(download_bytes) as download_bytes, 
    SUM(upload_bytes) as upload_bytes, 
    SUM(download_bytes + upload_bytes) as total_bytes, 
    DAY(date) as day, 
    MONTHNAME(date) as month 
FROM 
    traffic_counters 
LEFT JOIN services ON traffic_counters.service_id = services.id 
WHERE 
    date >= (NOW() - INTERVAL 1 MONTH)
GROUP BY 
    MONTH(date),
    DAY(date)
ORDER BY 
    MONTH(date), 
    DAY(date)