Print zero if nothing return by COUNT()

Question

My app need a line graph where i am going to show the number of new users joined by the country in last 3 days. i am plotting multiple lines on the same graph. so, i need to show empty value as well.

users table:

+------------+-------------------+----------------------------------------+
|id          |  First_name       |country_id       | created_at           |
+------------+-------------------+-----------------+----------------------+
| 1          | AAA               | 3               | 2014-02-23 15:55:55  |
| 2          | BBB               | 5               | 2014-02-22 15:55:55  |
| 3          | CCC               | 1               | 2014-02-22 17:55:55  |
| 4          | DDD               | 2               | 2014-02-22 15:55:55  |
| 5          | EEE               | 1               | 2014-02-22 16:55:55  |
| 6          | FFF               | 1               | 2014-02-23 15:55:55  |
+------------+-------------------+-----------------+----------------------+

the query:

Select COUNT(users.id) AS count, DATE(users.created_at) AS date , users.country_id
from `users` 
where `created_at` >= '2014-02-21' and `created_at` < '2014-02-24' and users.country_id IN(1, 3, 10)
group by `date`, users.country_id 
order by `date` asc

Expected output:

+------------+-------------------+------------------
|count       |  date             |country_id       | 
+------------+-------------------+-----------------+
| 0          | 2014-02-21        | 1               |
| 0          | 2014-02-21        | 3               |
| 0          | 2014-02-21        | 10              |
| 2          | 2014-02-22        | 1               | 
| 0          | 2014-02-22        | 3               | 
| 0          | 2014-02-22        | 10              | 
| 1          | 2014-02-23        | 1               | 
| 1          | 2014-02-23        | 3               | 
| 0          | 2014-02-23        | 10              | 
+------------+-------------------+-----------------+

The above query do not return any value if there is no data. How can i print 0 if no data found for a country for a day.

Why are you not interested in the results for country ID 2 and 5 (which are represented in the data)? — Jonathan Leffler, Feb 25 '14 at 07:01
In the expected output you have 10 as the country_id but not in the table — G one, Feb 25 '14 at 07:03
`select * from users where created_at = '2014-02-21' and country_id = 10` returns any row ? — Maheswaran Ravisankar, Feb 25 '14 at 07:04
@JonathanLeffler I want to show graph for top few countries. Lets say 10 one of them. — Anam, Feb 25 '14 at 07:12

score 0 · Answer 1 · answered Feb 25 '14 at 07:05

Try this,

Select Distinct
  (select COUNT(users.id) from `users` U where U.`date` = `date` and U.country_id = users.country_id) AS count
  ,DATE(users.created_at) AS date , users.country_id
from `users` 
where `created_at` >= '2014-02-21' and `created_at` < '2014-02-24' and users.country_id IN(1, 3, 10)
group by `date`, users.country_id 
order by `date` asc

score 0 · Answer 2 · answered Feb 25 '14 at 07:12

0

try this:

Select 
(case when COUNT(users.ID) > 0 then  COUNT(users.ID)
else 
 0
end) as count,DATE(users.created_at) AS date , users.country_id
from `users` 
where `created_at` >= '2014-02-21' and `created_at` < '2014-02-24' 
and users.country_id IN(1, 3, 10)
group by `date`, users.country_id 
order by `date` asc

answered Feb 25 '14 at 07:12

Rat-a-tat-a-tat Ratatouille

7,109
5
31
41

Working partially. however not printing 0 when no data available for a country. – Anam Feb 25 '14 at 08:04
no data available for a country means that its not present in the in query or the database itself?? Could you give an example? – Rat-a-tat-a-tat Ratatouille Feb 25 '14 at 08:12

score 0 · Answer 3 · edited May 23 '17 at 11:50

You can generate all the dates in the interval using a query. Use this query as nested query to get count.

SELECT COUNT(users.id) AS COUNT,
       DTTM.DATE AS date ,
       users.country_id
FROM `users` USR,
  (SELECT DATE
   FROM TABLE) DTTM
WHERE USR.created_at = DTTM.DATE
  AND users.country_id IN(1, 3, 10)
GROUP BY `date`,
         users.country_id
ORDER BY `date` ASC

Check this link for query to get all dates in between range.

MySQL display all date in between range

score 0 · Answer 4 · answered Feb 25 '14 at 07:14

0

Try something like this:

Select 
case 
when (COUNT(users.id) = 0) then 0 else COUNT(users.id) end AS count, 
DATE(users.created_at) AS date , 
users.country_id
from users 
where `created_at` >= '2014-02-21' 
and `created_at` < '2014-02-24' and users.country_id IN(1, 3)
group by `date`, users.country_id 
order by `date` asc

answered Feb 25 '14 at 07:14

G one

2,679
2
14
18

Not working. not much different than my query(not printing 0). – Anam Feb 25 '14 at 08:00

Jonathan Leffler · Answer 5 · 2014-02-25T16:06:09.923

You'll probably need to generate the Cartesian product of a list of dates and a list of country IDs, and then do a left outer join of that with the data in the users table and aggregate the results.

SELECT COUNT(U.ID), C.Date, C.Country_ID
  FROM (SELECT A.Country_ID, B.Date
          FROM (SELECT 1 AS Country_ID
                 UNION
                SELECT 3 AS Country_ID
                 UNION
                SELECT 10 AS Country_ID
               ) AS A
          JOIN (SELECT '2014-02-21' AS Date
                 UNION
                SELECT '2014-02-22' AS Date
                 UNION
                SELECT '2014-02-23' AS Date
               ) AS B
            ON 1 = 1
       ) AS C
  LEFT JOIN Users AS U
    ON U.Created_At BETWEEN C.Date AND DATE_ADD(C.Date, INTERVAL 1 DAY)
   AND U.Country_ID = C.Country_ID
 GROUP BY C.Date, C.Country_ID;

There are probably neater ways to generate the two lists; in general, you'd probably do best with a pair of temporary tables populated with the country IDs and dates that are of interest. The key point is that you have the dominant table (the one on the LHS of the left outer join) contain one row for each result row that you want, and then the actual (sparse) data in the table on the RHS of the join. The COUNT(U.ID) term counts the number of non-null values; if there is no match for the given date and country, the LOJ yields a NULL as the user ID, but the COUNT(U.ID) ignores those null values, giving 0 if there are no values at all.

Thanks for the answer. Some syntax is unknown in mysql (i.e DATEADD) — Anam, Feb 25 '14 at 08:14
Oh Futz; I mistyped the name, knew I had, and forgot to add the underscore after checking the syntax of the rest of the call. Surely though, you know where to look up date functions in the MySQL manual (don't admit it if you don't — [find them](http://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html)!) and could easily guess that maybe DATEADD should have been spelled DATE_ADD. — Jonathan Leffler, Feb 25 '14 at 16:07

score 0 · Answer 6 · answered Feb 25 '14 at 07:42

0

This should be the algo:

Generate all dates in the specified range and do a cross join with all countries
Left join the above resultset with users table on date and country id
Do a grouping on date and country id

answered Feb 25 '14 at 07:42

Dipendu Paul

2,685
1
23
20

Print zero if nothing return by COUNT()

6 Answers6