Get "Zero" for a Count at Dates Without Records

Get zero for a count at dates without records

Create a table that contains all dates. Then do a left join with the Activity table. Group on the date, and do a COUNT on Activity.id. The left join ensures that all dates from the date table are included in the result set, even if they are not matched in the join clause.

MySQL show count of 0 for dates with no records

Ok from my previous answer from the thread MySql Single Table, Select last 7 days and include empty rows

Here what you can do for making the date selection dynamic

select 
t1.attempt_date,
coalesce(SUM(t1.attempt_count+t2.attempt_count), 0) AS attempt_count
from
(
  select DATE_FORMAT(a.Date,'%Y/%m/%d') as attempt_date,
  '0' as  attempt_count
  from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a 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) as a
    cross join (select 0 as a 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) as b
    cross join (select 0 as a 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) as c
  ) a
  where a.Date BETWEEN NOW() - INTERVAL 7 DAY AND NOW()
)t1
left join
(
  SELECT DATE_FORMAT(attempt_date,'%Y/%m/%d') AS attempt_date, 
  COUNT(*) AS attempt_count
  FROM users_attempts
  WHERE DATE_SUB(attempt_date, INTERVAL 1 DAY) > DATE_SUB(DATE(NOW()), INTERVAL 1 WEEK) 
  GROUP BY DAY(attempt_date) DESC
)t2
on t2.attempt_date = t1.attempt_date
group by DAY(t1.attempt_date)
order by t1.attempt_date desc;

DEMO

How to deal: my 'count' query does not get zero, when records not exist

Use Join with Group By

SELECT cu.id,cu.name,COUNT(cu.id) AS [COUNT],cu.ipn,cu.address,cu.number,cu.email
FROM Customer cu 
JOIN Car ca ON cu.id = ca.customer_id
GROUP BY cu.id,cu.name,cu.ipn,cu.address,cu.number,cu.email

MYSQL return zero in date not present, and COUNT how many rows are present with a specific date

Consider:

select c.mydate, count(d.id) number_of_orders
from calendar c
left join delivery d on d.giorno = c.mydate
group by c.mydate

This works by left-joining the calendar table with the orders table, then aggregating by date, and finally counting the number of matching rows in the order table.

This is quite close to your original query (although this uses left join instead of right join), however this uses an aggregate function to count the orders.

mysql show count as 0 for non-existing records

You need to generate the dates dynamically for this and then use left join. Also note that since you are displaying the user_id it might be needed a cross join of distinct user_id with the dynamically generated dates.

From my previous answers related to showing missing dates MySql Single Table, Select last 7 days and include empty rows

Here is one for your case

select 
t1.user_id,
coalesce(t2.cnt,0) as cnt,
t1.view_date
from
(
  select DATE_FORMAT(a.Date,'%Y-%m-%d') as view_date,
  x.user_id
  from (
    select curdate() - INTERVAL (a.a + (10 * b.a) + (100 * c.a)) DAY as Date
    from (select 0 as a 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) as a
    cross join (select 0 as a 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) as b
    cross join (select 0 as a 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) as c
  ) a,(select distinct user_id from user_views)x
  where a.Date between DATE_SUB(DATE(NOW()), INTERVAL 90 DAY) AND now()
)t1
left join
(
  select user_id, count(user_id) as cnt, Date(view_time) as view_time from user_views
  where DATE(view_time) between DATE_SUB(DATE(NOW()), INTERVAL 90 DAY) AND now()
  group by user_id, date(view_time)
)t2
on t2.view_time = t1.view_date
and t1.user_id = t2.user_id
order by t1.view_date,t1.user_id

http://sqlfiddle.com/#!2/4136e/5

How to include zero / 0 results in COUNT aggregate?

You want an outer join for this (and you need to use person as the "driving" table)

SELECT person.person_id, COUNT(appointment.person_id) AS "number_of_appointments"
FROM person 
  LEFT JOIN appointment ON person.person_id = appointment.person_id
GROUP BY person.person_id;

The reason why this is working, is that the outer (left) join will return NULL for those persons that do not have an appointment. The aggregate function count() will not count NULL values and thus you'll not get a zero.

If you want to learn more about outer joins, here is a nice tutorial: http://sqlzoo.net/wiki/Using_Null

Count registers by day including zero

You can use a Calendar or dates table for this sort of thing.

For only 152kb in memory, you can have 30 years of dates in a table with this:

/* dates table */
declare @fromdate date = '20000101';
declare @years    int  = 30;
/* 30 years, 19 used data pages ~152kb in memory, ~264kb on disk */
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
select top (datediff(day, @fromdate,dateadd(year,@years,@fromdate)))
    [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
into dbo.Dates
from n as deka cross join n as hecto cross join n as kilo
               cross join n as tenK cross join n as hundredK
order by [Date];
create unique clustered index ix_dbo_Dates_date
  on dbo.Dates([Date]);

Without taking the actual step of creating a table, you can use it inside a common table expression with just this:

declare @fromdate date = dateadd(day  , datediff(day  , 0, getdate() )-30 , 0); 
declare @thrudate date = dateadd(day  , datediff(day  , 0, getdate() ), 0);
;with n as (select n from (values(0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) t(n))
, dates as (
  select top (datediff(day, @fromdate, @thrudate)+1) 
      [Date]=convert(date,dateadd(day,row_number() over(order by (select 1))-1,@fromdate))
  from n as deka cross join n as hecto cross join n as kilo
                cross join n as tenK cross join n as hundredK
   order by [Date]
)
select [Date]
from dates;

Use either like so:

select 
    d.Date
  , count(t.DownloadDate) as DownloadCount
from dates d
  left join TBL_Downloads t
    on d.date = convert(date,t.DownloadDate)
   and t.userid = 292
where d.date >= dateadd(day  , datediff(day  , 0, getdate() )-30 , 0)
  and d.date <= dateadd(day  , datediff(day  , 0, getdate() ), 0)
group by d.date

rextester demo: http://rextester.com/ISK37732 (dates changed to be within last 30 days)

returns:

+------------+---------------+
|    Date    | DownloadCount |
+------------+---------------+
| 2017-02-27 |             0 |
| 2017-02-28 |             0 |
| 2017-03-01 |             2 |
| 2017-03-02 |             1 |
| 2017-03-03 |             0 |
| 2017-03-04 |             1 |
| 2017-03-05 |             1 |
| 2017-03-06 |             1 |
| 2017-03-07 |             0 |
| 2017-03-08 |             0 |
| 2017-03-09 |             0 |
| 2017-03-10 |             0 |
| 2017-03-11 |             0 |
| 2017-03-12 |             0 |
| 2017-03-13 |             0 |
| 2017-03-14 |             0 |
| 2017-03-15 |             0 |
| 2017-03-16 |             0 |
| 2017-03-17 |             0 |
| 2017-03-18 |             0 |
| 2017-03-19 |             0 |
| 2017-03-20 |             0 |
| 2017-03-21 |             0 |
| 2017-03-22 |             0 |
| 2017-03-23 |             0 |
| 2017-03-24 |             0 |
| 2017-03-25 |             0 |
| 2017-03-26 |             0 |
| 2017-03-27 |             0 |
| 2017-03-28 |             0 |
| 2017-03-29 |             0 |
+------------+---------------+

Number and Calendar table reference:

Generate a set or sequence without loops - 1 - Aaron Bertrand
Generate a set or sequence without loops - 2 - Aaron Bertrand
Generate a set or sequence without loops - 3 - Aaron Bertrand
The "Numbers" or "Tally" Table: What it is and how it replaces a loop - Jeff Moden
Creating a Date Table/Dimension in sql Server 2008 - David Stein
Calendar Tables - Why You Need One - David Stein
Creating a date dimension or calendar table in sql Server - Aaron Bertrand
tsql Function to Determine Holidays in sql Server - Aaron Bertrand
F_table_date - Michael Valentine Jones

Get "Zero" for a Count at Dates Without Records