-1

There is my query:

SELECT DATE_FORMAT(post_date, '%Y-%m-%d') AS data_day
    , COUNT(*) AS count
FROM wpsa_posts
WHERE post_date >= DATE_ADD(CURDATE(),INTERVAL -7 DAY)
GROUP
    BY data_day
    ORDER BY data_day DESC
    LIMIT 7

THERE IS THE RESULT OF THE QUERY LIKE THAT:

enter image description here

IF I PUT LAST 30 DAYS THIS GONNA BE LIKE

enter image description here

I want to show all the dates if i put 7(appear the last 7 days and if have nothing on this day appear 0 on count)

Example like i want: i want to show all dates like that example:

data_day     ||   count ||
2018-08-07   ||   0 if nothing on this day
2018-08-08   ||   32
2018-08-09   ||   1
2018-08-10   ||   4
2018-08-11   ||   0
2018-08-12   ||   0
2018-08-13   ||   0

i want to show like that

manitoramos
  • 71
  • 1
  • 6
  • Your question is not clear to me. It would be easier to understand if you instead show sample input data, and the expected output. – Tim Biegeleisen Aug 07 '18 at 11:31
  • i want to show all dates like that example: data_day || count || 2018-08-07 || 0 if nothing on this day 2018-08-08 || 32 2018-08-09 || 1 2018-08-10 || 4 2018-08-11 || 0 2018-08-12 || 0 2018-08-13 || 0 i want to show like that – manitoramos Aug 07 '18 at 11:38
  • Based on the thin information you gave us, I might suggest that what you need is a calendar table having all dates which you want to appear in your output. – Tim Biegeleisen Aug 07 '18 at 11:39
  • See: [Why should I provide an MCVE for what seems to me to be a very simple SQL query?](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) – Strawberry Aug 07 '18 at 11:40
  • i want all dates to put on graphic and i need to have all the days because with a plugin i'm using on wordpress if no data is coming this gives error thats why i need to have 0 on count if didnt have post on this days – manitoramos Aug 07 '18 at 11:45
  • This issue is addressed in a similar issue: https://stackoverflow.com/questions/12761113/mysql-date-list-with-count-even-if-no-data-on-specific-date – bitsobits Aug 07 '18 at 11:53
  • Possible duplicate of [MySQL: Select All Dates In a Range Even If No Records Present](https://stackoverflow.com/questions/1046865/mysql-select-all-dates-in-a-range-even-if-no-records-present) – Nick Aug 07 '18 at 12:19

1 Answers1

1

you have generate date values and join with your table then you can count 

select date, count(post_date) from
(select  * from (
select 
 date_add('2018-07-17 00:00:00.000', INTERVAL n5.num*10000+n4.num*1000+n3.num*100+n2.num*10+n1.num DAY ) as date 
  from
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n1,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n2,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n3,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n4,
(select 0 as num
   union all select 1
   union all select 2
   union all select 3
   union all select 4
   union all select 5
   union all select 6
   union all select 7
   union all select 8
   union all select 9) n5
) a
where date >'2018-07-17 00:00:00.000' and date < NOW()
) as t
left join wpsa_posts w on t.date=DATE_FORMAT(w.post_date, '%Y-%m-%d')
 WHERE date >= DATE_ADD(CURDATE(),INTERVAL -7 DAY)
 group by date
Zaynul Abadin Tuhin
  • 31,407
  • 5
  • 33
  • 63