CODEWITHSUNDEEP
phpmysql
dietervdf
dietervdf

Reputation: 402

force count to be zero in a mysql query

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 |
|       .. |      .. |  
+----------+---------+

Upvotes: 2

Views: 124

Answers (4)

Drew
Drew

Reputation: 24959

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)

Upvotes: 0

Orangepill
Orangepill

Reputation: 24645

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;
}

Upvotes: 1

Mahdyfo
Mahdyfo

Reputation: 1173

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

Upvotes: 1

Rob W
Rob W

Reputation: 9142

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.

Upvotes: 0

Related Questions