CODEWITHSUNDEEP
mysqlsql
sravis
sravis

Reputation: 3680

MySql count() to return 0 if no records found

I have a set of posts on monthly basis. Now i need an array which contains total records of posts posted in each month. I tried below MySql query, Its working fine, but I was expecting 0(Zero) for months where there is no records. Here its not returning 0.

I read that COUNT() will not return '0', So how do i achieve this?

I tried IFNULL(), and COALESCE() but still getting the same result. Please help with this query. Thank You......

SELECT
count(id) as totalRec
FROM ('post')
WHERE year(date) =  '2013'
AND monthname(date) IN ('January', 'February', 'March') 
GROUP BY year(date)-month(date)
ORDER BY 'date' ASC

Got Result:

+----------+
| totalRec |
+----------+
|        7 |
|        9 |
+----------+

Expected Result (Where there is no posts for January):

+----------+
| totalRec |
+----------+
|        0 |
|        7 |
|        9 |
+----------+

Sample Data:

+----+---------------------+
| id | date                |
+----+---------------------+
| 24 | 2012-12-16 16:29:56 |
|  1 | 2013-02-25 14:57:09 |
|  2 | 2013-02-25 14:59:37 |
|  4 | 2013-02-25 15:12:44 |
|  5 | 2013-02-25 15:14:18 |
|  7 | 2013-02-26 11:31:31 |
|  8 | 2013-02-26 11:31:59 |
| 10 | 2013-02-26 11:34:47 |
| 14 | 2013-03-04 04:39:02 |
| 15 | 2013-03-04 05:44:44 |
| 16 | 2013-03-04 05:48:29 |
| 19 | 2013-03-07 15:22:34 |
| 20 | 2013-03-15 12:24:43 |
| 21 | 2013-03-16 16:27:43 |
| 22 | 2013-03-16 16:29:28 |
| 23 | 2013-03-16 16:29:56 |
| 11 | 2013-03-17 11:35:12 |
+----+---------------------+

Upvotes: 14

Views: 67776

Answers (7)

钟智强
钟智强

Reputation: 1

you can try to simply you sql. My old sql like this

select c.*, dynamic_count from channel c 
    left join (select channel_id, count(*) as dynamic_count from dynamic group by channel_id) d
             on c.id = d.channel_id;

and i got null if dynamic don't match.But if i change my sql like this:

select c.*, count(d.id) as dynamic_count from channel c
    left join dynamic d on c.id = d.channel_id
    group by c.id;

I got 0 if no dynamics match; Hope it's helpful!

Upvotes: 0

HSP
HSP

Reputation: 395

Try Like this::

SELECT
  count(id) as totalRec
  IF(id NULL, 0, id) As IdList
FROM ('post')
 WHERE year(date) =  '2013'
  AND monthname(date) IN ('January', 'February', 'March') 
  GROUP BY year(date)-month(date)
ORDER BY 'date' ASC

To return 0 instead of null in MySQL
USE

SELECT id, IF(age IS NULL, 0, age) FROM tblUser

USE with count() having join of 2 tables

Tables

tblA

tblA_Id    Name     
-----------------
    1       HSP     
    2       MANI     
    3       MEET     
    4       user    
    5       jaani

tblB 

tblB_Id    SongName  tblA_Id
 -----------------------------
    1        song1     3
    2        song2     3
    3        song3     1
    4        song4     1
    5        song4     5

Query

SELECT 
    tblA.tblA_Id,
    tblA.Name,
    tblC.SongCount,
    IF(tblC.SongCount IS NULL, 0, tblC.SongCount) As noOfSong
  FROM  tblA
    LEFT JOIN
    (   
        SELECT 
            ArtistId,count(*) AS SongCount 
        FROM 
            tblB  
        GROUP BY 
            ArtistId
    ) AS tblC
    ON 
        tblA.tblA_Id = NoOfSong.ArtistId

Result is

 tblA_Id    Name     SongCount   noOfSong
 -------------------------------------------
    5       jaani    1           1
    4       user     NULL        0
    3       MEET     2           2
    2       MANI     NULL        0
    1       HSP      2           2

Upvotes: 1

kiriloff
kiriloff

Reputation: 26333

Did you try IFNULL() the right way? Maybe try IFNULL(Count(id), 0) in a SELECT clause with join.

Upvotes: 5

MRRaja
MRRaja

Reputation: 1161

I think you simply need query like this

SELECT COALESCE(COUNT(id),0) AS totid FROM table

vb example

Set count=Con.Execute("SELECT COALESCE(COUNT(id),0) AS totid FROM table")

then write

<%=count("totid")%>

Upvotes: 0

Paul Stanley
Paul Stanley

Reputation: 4098

COALESCE is what you could use, if you have a table of dates and left joined against it. It goes left to right to return the first non null value. Your group by does look a little nutty at the minute, I have adjusted it.

SELECT
COALESCE(count(id),0) as totalRec
FROM ('post')
LEFT JOIN dates
ON dates.date = post.date
WHERE year(date) =  '2013'
AND monthname(date) IN ('January', 'February', 'March') 
GROUP BY month(date), year(date)
ORDER BY 'date' ASC

Where dates table is..

DATE
2013-01-01 
2013-01-02
2013-01-03
etc....

See here : http://easywebapps.blogspot.co.uk/2011/07/mysql-how-to-create-calendar-table.html

Upvotes: 5

John Woo
John Woo

Reputation: 263713

There is no record for the month of January that is why you are getting no result. One solution that works is by joining a subquery with contains list of months that you want to be shown on the list.

SELECT count(b.id) as totalRec
FROM   (
            SELECT 'January' mnth
            UNION ALL
            SELECT 'February' mnth
            UNION ALL
            SELECT 'March' mnth
        ) a
        LEFT JOIN post b
            ON a.mnth = DATE_FORMAT(b.date, '%M') AND
               year(b.date) =  '2013' AND 
               DATE_FORMAT(b.date, '%M') IN ('January', 'February', 'March') 
GROUP  BY year(b.date)-month(b.date) 
ORDER  BY b.date ASC

OUTPUT

╔══════════╗
║ TOTALREC ║
╠══════════╣
║        0 ║
║        7 ║
║        9 ║
╚══════════╝

Upvotes: 19

Justin
Justin

Reputation: 458

If the result set had no posts during that time period, you won't get any results to count, hence why it doesn't show.

You would need to either join to another table that has all of the year months or fill in the data programmatically when the results are returned. I can't think of another way to do this, but perhaps it's possible.

Also, as others have said, separate the group by a comma.

Upvotes: 1

Related Questions