sql - MySql count() to return 0 if no records found -
i have set of posts on monthly basis. need array contains total records of posts posted in each month. tried below mysql query, working fine, expecting 0(zero) months there no records. here not returning 0.
i read count() not return '0', how achieve this?
i tried ifnull(), , coalesce() still getting same result. please query. thank you......
select count(id) totalrec ('post') year(date) = '2013' , monthname(date) in ('january', 'february', 'march') group year(date)-month(date) order 'date' asc got result:
+----------+ | totalrec | +----------+ | 7 | | 9 | +----------+ expected result (where there no posts 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 | +----+---------------------+
there no record month of january why getting no result. 1 solution works joining subquery contains list of months want shown on list.
select count(b.id) totalrec ( select 'january' mnth union select 'february' mnth union select 'march' mnth ) left join post b on a.mnth = date_format(b.date, '%m') , year(b.date) = '2013' , date_format(b.date, '%m') in ('january', 'february', 'march') group year(b.date)-month(b.date) order b.date asc output
╔══════════╗ ║ totalrec ║ ╠══════════╣ ║ 0 ║ ║ 7 ║ ║ 9 ║ ╚══════════╝
Comments
Post a Comment