force count to be zero in a mysql query

Question

I have two tables t1, t2 and the following query:

SELECT t2.year,
       Count(t1.id) AS count
FROM   t1,
       t2
WHERE  t2.t1id = t1.id
       AND t2.year IN ( 1995, 1996, 1997, 1998,
                    1999, 2000 )
GROUP  BY t2.year
ORDER  BY t1.year

Which results in:

+----------+--------+
| year     | count  |
+----------+--------+
|     1995 |      1 |
|     1998 |      3 |
|     1999 |      3 |
|     2000 |     28 |
+----------+--------+

And as you can see some years are missing. Is it possible to rewrite this query such that it results in?

+----------+--------+
| year     | count  |
+----------+--------+
|     1995 |      1 |
|     1996 |      0 |
|     1997 |      0 |
|     1998 |      3 |
|     1999 |      3 |
|     2000 |     28 |
+----------+--------+

I could use php and check which rows are missing to fill in the missing gaps, but that doesn't seem very efficient.. Any ideas?

edit

t1

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| name      | varchar(128) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

t2

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| t1id      | int(11)      | NO   |     | NULL    |                |
| year      | int(11)      | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

For example:

t1

+----------+---------+
| id       | name    |
+----------+---------+
|        1 |    john |
|        2 |     bob |
|       .. |      .. |
+----------+---------+

t2

+----------+---------+---------+
| id       | t1id    |    year |
+----------+---------+---------+
|      100 |       1 |    1995 |
|      101 |       2 |    1998 |
|      103 |       3 |    1998 |
|       .. |      .. |      .. |  
+----------+---------+---------+

Where after the combination I end up with:

+----------+---------+
| id       |    year |
+----------+---------+
|      100 |    1995 |
|      101 |    1998 |
|      103 |    1998 |
|       .. |      .. |  
+----------+---------+

Just a sec, i'm rewriting/clarifying the post. @DrewPierce hmm, is using `WHERE` that bad? — dietervdf, Jul 21 '15 at 18:24
if the left table has the years you can left join and use ifnull or coalesce — Drew, Jul 21 '15 at 18:29
@DrewPierce the problem is that the table `t2` doesn't have the years... Any way to include the `IN` list in the query results? — dietervdf, Jul 21 '15 at 18:39
Without a table containing all the years I think you are going to have to deal with the missing elements via code. — Orangepill, Jul 21 '15 at 18:41
very common to have a lean and mean table with a bunch of stuff for the sole purpose of left joins. see my answer for consideration — Drew, Jul 21 '15 at 19:12

score 1 · Answer 1 · answered Jul 21 '15 at 18:18

1

SELECT t2.year,
       IF(Count(t1.id) > 0, Count(t1.id), 0)
FROM   t1,
       t2
WHERE  t2.t1id = t1.id
       AND t2.year IN ( 1995, 1996, 1997, 1998,
                    1999, 2000 )
GROUP  BY t2.year
ORDER  BY t1.year

answered Jul 21 '15 at 18:18

Mahdyfo

1,155
7
18

score 1 · Answer 2 · answered Jul 21 '15 at 18:49

Without a source of all possible years that your query could cover you are going to have to use php to do this. One approach would could look something like this.

function getCountsForRange(\PDO $dbConn, $startYear, $endYear){
    $ret = array_fill_keys(range($startYear, $endYear), 0);
    $stmt = $dbConn->prepare("SELECT t2.year,Count(t1.id) AS count ".
                             "FROM t1,t2 ".
                             "WHERE  t2.t1id = t1.id AND t2.year between ? and ? ".
                             "GROUP  BY t2.year ORDER  BY t1.year");

    $stmt->execute([$startYear, $endYear]);
    while ($row = $stmt->fetch(\PDO::FETCH_ASSOC)){
        $ret[$row["year"]] = $row["count"];
    }
    return $ret;
}

score 0 · Answer 3 · edited May 23 '17 at 12:14

0

You will need to handle the empty rows pragmatically or in the query itself depending on the situation.

See:

MySQL GROUP BY and Fill Empty Rows

or

Populating query results with empty rows

For some ideas.

edited May 23 '17 at 12:14

Community

1
1

answered Jul 21 '15 at 18:18

Rob W

9,134
1
30
50

Ideas instead of answers? What a *unique* idea! ¯\\_(ツ)_/¯ – Jay Blanchard Jul 21 '15 at 18:25
According to these links I should handle the situation using php. Oh well, I guess that works. – dietervdf Jul 21 '15 at 18:41

score 0 · Accepted Answer · answered Jul 21 '15 at 19:10

create table yrCheat
(   year int not null
);

create table t1
(   -- forgive lack of PK
    id int not null,
    name varchar(128) not null
);

create table t2
(   -- forgive lack of PK
    id int not null,
    t1id int not null,
    year int not null
);

insert t1(id,name) values (100,'john'),(101,'bob'),(102,'sally');
insert t2(id,t1id,year) values (100,1,1995),(101,2,1998),(101,3,1998),(101,4,1998);
insert into yrCheat (year) values (1990),(1991),(1992),(1993),(1994),(1995),(1996),(1997),(1998),(1999),(2000);
-- etc

select yc.year,count(t1.id) as count
from yrCheat yc
left join t2
on t2.year=yc.year -- and yc.year in (1995,1996,1997,1998,1999,2000)
left join t1
on t1.id=t2.id
where yc.year in (1995,1996,1997,1998,1999,2000)
group by yc.year
order by yc.year

+------+-------+
| year | count |
+------+-------+
| 1995 |     1 |
| 1996 |     0 |
| 1997 |     0 |
| 1998 |     3 |
| 1999 |     0 |
| 2000 |     0 |
+------+-------+
6 rows in set (0.00 sec)

force count to be zero in a mysql query

edit

4 Answers4