CODEWITHSUNDEEP
mysqlsql
user2794041
user2794041

Reputation:

how to get data according to month only?

I have writen a query but it gets data according to date month year but I want to get data group by month only how i do this? Here is my query:

select a.entered,a.credit,
(select a.account_id from listing,article r where a.account_id=r.account_id group by 
a.account_id) as article,(select a.account_id from listing,event e where 
a.account_id=e.account_id group by a.account_id) as event,a.renewal_date from listing a 
where a.profit_instructor_id='0' group by  DATE_FORMAT(a.entered,'%m-%d-%Y')

Upvotes: 1

Views: 56

Answers (2)

evuez
evuez

Reputation: 3387

You can use EXTRACT() or MONTH():

GROUP BY EXTRACT(MONTH FROM a.entered)
GROUP BY MONTH(a.entered)

With you full query and EXTRACT:

SELECT a.entered,
       a.credit,

  (SELECT a.account_id
   FROM listing,
        article r
   WHERE a.account_id=r.account_id
   GROUP BY a.account_id) AS article,

  (SELECT a.account_id
   FROM listing,
        event e
   WHERE a.account_id=e.account_id
   GROUP BY a.account_id) AS event,
       a.renewal_date
FROM listing a
WHERE a.profit_instructor_id='0'
GROUP BY EXTRACT(MONTH
                 FROM a.entered)

Upvotes: 1

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

You can use MONTH() function in group by it will group the months in all years you should group months with year also

SELECT 
  a.entered,
  a.credit,
  (SELECT 
    a.account_id 
  FROM
    listing,
    article r 
  WHERE a.account_id = r.account_id 
  GROUP BY a.account_id) AS article,
  (SELECT 
    a.account_id 
  FROM
    listing,
    event e 
  WHERE a.account_id = e.account_id 
  GROUP BY a.account_id) AS event,
  a.renewal_date 
FROM
  listing a 
WHERE a.profit_instructor_id = '0' 
GROUP BY MONTH(DATE_FORMAT(a.entered, '%m-%d-%Y')),
YEAR(DATE_FORMAT(a.entered, '%m-%d-%Y'))

Upvotes: 0

Related Questions