CODEWITHSUNDEEP
phpmysqlsql
Jiggles
Jiggles

Reputation: 73

Count total values for given month

I have this mySQL statement:

SELECT COUNT(clicks) FROM ads.statz WHERE user_id = '$_SESSION[user_id]'

Works fine it counts all the number of clicks with the given user id. What I am after is being able to group together the numbers for each month as a total. I need this for both Clicks and impressions.

Here is the layout of the DB. 

CREATE TABLE `statz` (
  `ad_id` int(50) NOT NULL,
  `date` datetime NOT NULL,
  `clicks` int(50) NOT NULL,
  `impressions` int(50) NOT NULL,
  `user_id` int(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Thanks!

Upvotes: 0

Views: 98

Answers (1)

John Conde
John Conde

Reputation: 219804

Use MONTH() and YEAR() with GROUP BY

SELECT 
    COUNT(clicks) 
FROM 
    ads.statz 
WHERE 
    user_id = '$_SESSION[user_id]' 
GROUP BY
    MONTH(date),
    YEAR(date)

Upvotes: 4

Related Questions