Count() does not return zero MySQL

Question

I am a web developer and tried to search for the solution on a mysql query.I am unable to get the right solution for the count() function to return zero. The count() function doesnot return zero for all dates.

The query is as below . can anyone help me on this.

SELECT
    count(stat_id) as typeSuccess,
    device_type as typeName,
    YEARWEEK(date_auth) as date
FROM auth_stat
WHERE
    AUTH_RESULT = 'SUCCESS' AND
    date_auth BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 WEEK) AND CURDATE()
GROUP BY YEARWEEK(date_auth), device_type
ORDER BY YEARWEEK(date_auth)

The query that i tried to form is

select
    date_auth,
    count(stat_id) as typeSuccess,
    device_type as typeName,
    YEARWEEK(date_auth) as date
from
( 
    select @curDate := Date_Add(@curDate, interval 1 day) as MyJoinDate
    from
    ( 
        select @curDate := CURDATE()
    ) sqlvars, auth_stat limit 18 
) dateAll
LEFT JOIN auth_stat U on dateAll.MyJoinDate = U.date_auth
group by dateAll.MyJoinDate

Actual ouput :

+------------+-------------+
| date       | typeSuccess |
+------------+-------------+
| 2015-03-18 |          11 |
+------------+-------------+

Expected Output:

+------------+-------------+
| date       | typeSuccess |
+------------+-------------+
| 2015-03-18 |          11 |
| 2015-03-19 |          0  |
| 2015-03-20 |          0  |
+------------+-------------+

The inner select is not returning any rows for those dates and counting nothing is .. well, not counting at all .. as there are no rows to count for the given group (read: the groups do not exist). You have to perform a left join on the date and default the typeSuccess to 0 or otherwise synthesize the missing rows. — user2864740
– user2864740, Commented Mar 23, 2015 at 18:42
Which table has all dates you want to appear in the expected output (with or without a COUNT of 0)? — DWright
– DWright, Commented Mar 23, 2015 at 18:48
how about some data? if you are trying to count something i would start with just a regular select without a Cartesian Product. I don't get the point of the variable since the process order of those is not guarenteed — John Ruddell
– John Ruddell, Commented Mar 23, 2015 at 19:04
Jayant if my answer solved your issue can you please accept it? it is the check mark underneath the number of votes for an answer. if you could it would be greatly appreciated — John Ruddell
– John Ruddell, Commented Mar 25, 2015 at 5:25

John Ruddell · Accepted Answer · 2015-03-23 19:32:18Z

I believe all you want to do is just this

SELECT
    SUM(AUTH_RESULT='SUCCESS') as numSuccess,
    YEARWEEK(date_auth) as date
FROM auth_stat
WHERE date_auth BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 WEEK) AND CURDATE()
GROUP BY YEARWEEK(date_auth), device_type
ORDER BY YEARWEEK(date_auth)

basically all i'm doing is telling MySQL to sum up the boolean value (0 or 1) when auth_result is a success.. SUM() will return a 0 for a particular week if there is no successes in that week

The main issue is you were filtering out all non successful auth_results which would then not be counted. so remove that from the where and you should be good!

if you want it per day then you can do this

SELECT
    SUM(AUTH_RESULT='SUCCESS') as numSuccess,
    DATE(date_auth) as date
FROM auth_stat
WHERE date_auth BETWEEN DATE_SUB(CURDATE(), INTERVAL 6 WEEK) AND CURDATE()
GROUP BY DATE(date_auth), device_type
ORDER BY DATE(date_auth)

SQLFIDDLE FOR BOTH RESULTS

John Ruddell · Accepted Answer · 2015-03-23 19:28:57Z

0

try

SELECT COUNT(statid), dates.auth_date 
FROM 
(   SELECT DISTINCT auth_date 
    FROM Table1 
    WHERE auth_date BETWEEN '2015-03-06' AND '2015-03-09' -- or whatever
) AS dates
LEFT JOIN Table1 ON TRUE
 AND Table1.auth_date = dates.auth_date
 AND table1.auth_result = 'SUCCESS'
GROUP BY dates.auth_date
ORDER BY dates.auth_date

as desribed here: http://sqlfiddle.com/#!9/84faf/2 for eg data:

+--------+-------------+------------+
| statid | auth_result | auth_date  |
+--------+-------------+------------+
|      1 | SUCCESS     | 2015-03-05 |
|      2 | SUCCESS     | 2015-03-05 |
|      3 | SUCCESS     | 2015-03-05 |
|      4 | OTHER       | 2015-03-06 |
|      5 | OTHER       | 2015-03-06 |
|      6 | OTHER       | 2015-03-06 |
|      7 | SUCCESS     | 2015-03-07 |
|      8 | SUCCESS     | 2015-03-07 |
|      9 | SUCCESS     | 2015-03-07 |
|     10 | OTHER       | 2015-03-08 |
|     11 | SUCCESS     | 2015-03-08 |
|     12 | OTHER       | 2015-03-08 |
|     13 | SUCCESS     | 2015-03-09 |
|     14 | SUCCESS     | 2015-03-09 |
|     15 | SUCCESS     | 2015-03-09 |
|     16 | OTHER       | 2015-03-10 |
|     17 | OTHER       | 2015-03-10 |
|     18 | SUCCESS     | 2015-03-10 |
|     19 | OTHER       | 2015-03-11 |
+--------+-------------+------------+

eg output:

+---------------+-------------------------+
| count(statid) |        auth_date        |
+---------------+-------------------------+
|             0 | March, 06 2015 00:00:00 |
|             3 | March, 07 2015 00:00:00 |
|             1 | March, 08 2015 00:00:00 |
|             3 | March, 09 2015 00:00:00 |
+---------------+-------------------------+

edited Mar 23, 2015 at 19:28

John Ruddell

25.9k7 gold badges60 silver badges88 bronze badges

answered Mar 23, 2015 at 19:04

murison

4,0552 gold badges26 silver badges44 bronze badges

1 Comment

John Ruddell Over a year ago

you can very easily just use the output in a table builder like this one to format it for you :) sensefulsolutions.com/2010/10/format-text-as-table.html -------------- btw you have invalid syntax in your answer incase you want to fix that for the OP

Alex · Accepted Answer · 2015-03-23 20:10:43Z

It is not beautiful but hope it helps:

SELECT
    date_auth,
    count(stat_id) as typeSuccess,
    device_type as typeName,
    YEARWEEK(date_auth) as date
FROM
( 
    SELECT 
   DATE(DATE_ADD(NOW(), interval tmp.id day)) AS MyJoinDate
   FROM (SELECT 1 as id
         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
         UNION ALL SELECT 10
         UNION ALL SELECT 11
         UNION ALL SELECT 12
         UNION ALL SELECT 13
         UNION ALL SELECT 14
         UNION ALL SELECT 15
         UNION ALL SELECT 16
         UNION ALL SELECT 17
         UNION ALL SELECT 18
   ) AS tmp 
) dateAll
LEFT JOIN auth_stat U on dateAll.MyJoinDate = U.date_auth
group by dateAll.MyJoinDate

MJayK · Accepted Answer · 2015-03-24 20:00:11Z

0

I have tried adding the query as below. It stills returns me records that have the counts and not the dates with the count as zero.

SELECT COUNT(stat_id), dates.date_auth 
FROM 
(   SELECT DISTINCT date_auth 
    FROM auth_stat 
    WHERE date_auth BETWEEN DATE_SUB(CURDATE(), INTERVAL 20 DAY) AND       CURDATE()
) AS dates
LEFT JOIN auth_stat ON TRUE
 AND auth_stat.date_auth = dates.date_auth
 AND auth_stat.auth_result = 'SUCCESS'
GROUP BY dates.date_auth
ORDER BY dates.date_auth

Output result for the above query:

COUNT(stat_id) date_auth Ascending 1
1 2015-03-05 00:00:00 1 2015-03-06 00:00:00 11 2015-03-18 00:00:00

answered Mar 24, 2015 at 20:00

MJayK

11 silver badge2 bronze badges

Collectives™ on Stack Overflow

Count() does not return zero MySQL

Expected Output:

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Expected Output:

4 Answers 4

Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related