CODEWITHSUNDEEP
mysqlsqldatetimeselectgroup-by
UI Dev
UI Dev

Reputation: 699

Retrieve repeated month data in separate rows

MySql query is like this

SELECT MONTHNAME(access_date) as date,
  DATE_FORMAT( access_date,  '%d/%m/%Y' ) as month_date, 
  COUNT( log_id ) as total_count
    FROM user_activity_log
    WHERE dam_id = (
        SELECT dam_id 
            FROM dam_content_details
            WHERE content_type= 'userLogin'
        ) 
    AND CAST(access_date as DATE) BETWEEN '2012-09-01'
        AND '2014-01-01'
    GROUP BY MONTH( access_date )
    ORDER BY access_date ASC

  1. The problem i faced is the data of November & December in 2012 year is adding with November & December of 2013 year & showing in a one row. But i want to be separate rows for this.

  2. The second one is its only showing the first 12 months not up to 2014 January.

My sample output is like this

date        month_date  total_count
---------  ------------ -----------
September   15/09/2012  7
October     05/10/2012  34
November    05/11/2012  21
December    07/12/2012  49
January     01/01/2013  45
February    02/02/2013  107
March       01/03/2013  158
April       01/04/2013  100
May         01/05/2013  393
June        01/06/2013  272

Upvotes: 2

Views: 69

Answers (1)

Saharsh Shah
Saharsh Shah

Reputation: 29051

Try this: 

SELECT Monthname(access_date)               AS DATE, 
       Date_format(access_date, '%d/%m/%Y') AS month_date, 
       Count(log_id)                        AS total_count 
FROM   user_activity_log 
WHERE  dam_id = (SELECT dam_id 
                 FROM   dam_content_details 
                 WHERE  content_type = 'userLogin') 
       AND Cast(access_date AS DATE) BETWEEN '2012-09-01' AND '2014-01-01' 
GROUP  BY Year(access_date), 
          Month(access_date) 
ORDER  BY access_date ASC

OR

Query WITH JOIN 

SELECT Monthname(ual.access_date)               AS DATE, 
       Date_format(ual.access_date, '%d/%m/%Y') AS month_date, 
       Count(DISTINCT ual.log_id)               AS total_count 
FROM   user_activity_log ual 
       INNER JOIN dam_content_details dcd 
               ON ual.dam_id = dcd.dam_id 
                  AND dcd.content_type = 'userLogin' 
WHERE  ual.access_date BETWEEN '2012-09-01' AND '2014-01-01' 
GROUP  BY Year(ual.access_date), 
          Month(ual.access_date) 
ORDER  BY ual.access_date ASC

Upvotes: 1

Related Questions