CODEWITHSUNDEEP
mysql
Raymond Clayton Rudman
Raymond Clayton Rudman

Reputation: 75

Get SUM data for each month for one years interval for chart.js

I have a table with IP accounting data,

I am wanting to get the SUM(bytes) for each month(jan, feb, mar...) for the interval of one year from the current day

column: bytes <-- bytes

column: timeanddate <-- timestamp

My query:

SELECT (SELECT SUM(bytes) FROM ip_accounting);

Please help.

Upvotes: 1

Views: 579

Answers (1)

Raymond Clayton Rudman
Raymond Clayton Rudman

Reputation: 75

I have figured out the answer I was looking for 

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)

Upvotes: 0

Related Questions