CODEWITHSUNDEEP
mysql
Maklee Lee
Maklee Lee

Reputation: 283

how to get data weekly wise in mysql for each month

SELECT DATE_FORMAT(es.scheduled_datetime, '%X-%V') AS date,
    COUNT(es.event_schedule_id) AS total,
    0 as type
    FROM  event_schedule es ,event_schedule_mapping esm,events e
    WHERE 
    es.event_schedule_id  = esm.event_schedule_id and
    esm.event_id = e.event_id and
    es.event_status_id in(1,2) and
    es.scheduled_datetime BETWEEN 
    '2017-01-01' AND '2017-01-31'
    GROUP BY date
    ORDER BY date

this is my Query using this Query i am able to display record whose count is greater than zero and below output come like this

for January month 

'2017-01', '2', '0'
'2017-02', '2', '0'
'2017-03', '10', '0'
'2017-04', '2', '0'
'2017-05', '9', '0'

But its not displaying if count is zero in second week while i have to display that also :

'2017-01', '2', '0'
'2017-02', '0', '0'
'2017-03', '10', '0'
'2017-04', '2', '0'
'2017-05', '9', '0'

please suggest me how to display Record if count is zero it should week wise please suggest me

Upvotes: 0

Views: 852

Answers (1)

Jatin Raghav
Jatin Raghav

Reputation: 62

if you want to get data weekly in each month from database , I have query for that try this query.

SELECT DISTINCT EXTRACT(WEEK FROM date) as week,tbl_data.date AS date, SUM(count.calories) AS sum,tbl_data.offset AS offset from tbl_data INNER JOIN count ON count.id = tbl_data.item_id where tbl_data.user_id="+user_id+" and tbl_data.date and  MONTH(date) = MONTH(CURRENT_DATE()) GROUP BY week

Upvotes: 1

Related Questions