mysql - show zeros using count()

[Karbono]

Hi. I'm having some trouble ploting a graph because some columns won't show when their valu count equals zero.

 

I tried to adapt what’s on this topic (the issue is very similar) and I produced this query:

 

select Y.terminal, count(Z.id)

from (select distinct terminal from stats) Y

left outer join stats Z on Y.terminal=Z.terminal

where timestamp>'2012-02-15'

group by Y.terminal order by Y.terminal;

 

But the result is the same… 

 

Instead of this:

 

+---------------+-------------+
| terminal      | count(Z.id) |
+---------------+-------------+
|                        |        1194      |
| warehouse0  |          0           |
| warehouse2 |          0           
| warehouse2|          41         |
| warehouse3|          41   |
| warehouse4|           1          |
+---------------+-------------+

 

I get this:

 

+---------------+-------------+
| terminal      | count(Z.id) |
+---------------+-------------+
|                    |        1194      |
| warehouse2|          41         |
| warehouse3|          41   |
| warehouse4|           1          |
+---------------+-------------+

 

Can somebody help me out with this? I tried coalesce and ifnull, but the only thing I got was a messed up count.

----------

Here is my table's setup. (Obviously this sample data won't output the results above, but should be good for testing.)

 

 

 

CREATE TABLE `stats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `terminal` varchar(20) COLLATE latin1_general_ci NOT NULL,
  `timestamp` datetime NOT NULL,
  `pagina` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  `funcao` varchar(50) COLLATE latin1_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=2565 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci

 

INSERT INTO `stats` (`id`, `terminal`, `timestamp`, `pagina`, `funcao`) VALUES
(1, '', '2012-01-30 16:56:17', '/index.php', NULL),
(2, '', '2012-01-30 15:56:22', '/inserestats.php', NULL),
(3, 'warehouse1', '2012-01-30 16:56:58', '/index.php', NULL),
(4, 'warehouse1', '2012-01-30 16:57:04', '/index.php', NULL),
(5, '', '2012-01-30 17:09:17', '/index.php', NULL),
(6, '', '2012-01-30 17:09:20', '/index.php', NULL),
(7, '', '2012-01-30 17:09:26', '/index.php', NULL),
(8, '', '2012-01-30 17:09:32', '/index.php', NULL),
(9, '', '2012-01-30 17:09:36', '/index.php', NULL),
(10, '', '2012-01-30 17:09:38', '/index.php', NULL),
(11, '', '2012-01-30 17:10:35', '/index.php', NULL),
(2416, '', '2012-02-14 16:08:11', '/actualiza_form.php', ''),
(2415, '', '2012-02-14 16:08:07', '/index.php', 'testeindex'),
(15, '', '2012-01-30 17:14:07', '/index.php', NULL),
(16, warehouse2, '2012-01-15 17:14:48', '/index.php', NULL),
(17, warehouse2, '2012-01-15 17:15:09', '/index.php', NULL),
(18, warehouse2, '2012-01-15 17:15:33', '/index.php', NULL),
(19, warehouse2, '2012-01-15 17:15:59', '/index.php', NULL),
(20, warehouse2, '2012-01-15 17:16:04', '/index.php', NULL),
(21, warehouse2, '2012-01-15 17:16:06', '/actualiza_form.php', NULL))

 

------------

 

 

+-------------------------+------------------+
| Variable_name           | Value            |
+-------------------------+------------------+
| version                 | 5.1.58-1ubuntu1  |
| version_comment         | (Ubuntu)         |
| version_compile_machine | i686             |
| version_compile_os      | debian-linux-gnu |
+-------------------------+------------------+

 

[Karbono]

Thank you for formatting, Zane. Will be more careful next time.

[Karbono]

select stats.terminal, ifnull(count(id),0) 
from stats right join (select distinct terminal from stats) as terminais
on stats.terminal=terminais.terminal
where timestamp>'2012-02-15'
group by stats.terminal

 

still trying, still nothing...

[Karbono]

select stats.terminal, count(*) 
from stats left join (select distinct terminal from stats) as terminais 
on stats.terminal =terminais.terminal
where timestamp>'2012-02-15' group by 1 

 

Doesn't work.

[fenway]

Doesn't work how? What is the output expected? What is the output you get?

[Karbono]

Doesn't work how? What is the output expected? What is the output you get?

 

 

 

Instead of this:

 

+---------------+-------------+
| terminal      | count(Z.id) |
+---------------+-------------+
|                        |        1194      |
| warehouse0  |          0           |
| warehouse1 |          0           |     <--- this warehouse is wrong in the first post. I called it warehouse2 by mistake.
| warehouse2|          41         |
| warehouse3|          41   |
| warehouse4|           1          |
+---------------+-------------+

 

I get this:

 

+---------------+-------------+
| terminal      | count(Z.id) |
+---------------+-------------+
|                    |        1194      |
| warehouse2|          41         |
| warehouse3|          41   |
| warehouse4|           1          |
+---------------+-------------+

 

 

[fenway]

Sounds like the where clause is operating on a left join'ed field.

[Karbono]

Sounds like the where clause is operating on a left join'ed field.

 

Not sure what you mean. The where clause only serves the purpose of limiting the lines so that a few of the terminals aren't listed. the purpose is to make the query output "0" for all terminals that don't show up in the lines after that restriction.

[Karbono]

Here are a couple of similar requests:

 

http://stackoverflow.com/questions/2255531/mysql-returning-zero-when-no-value-is-present-categorized-monthly

 

http://stackoverflow.com/questions/1528688/mysql-count-return-zero-if-no-record-found

 

I haven't been able to adapt those solutions to my table.

[kicken]

select stats.terminal, ifnull(count(id),0) 

 

You need to convert the column to zero before the count is applied, not after.

 

select stats.terminal, count(coalesce(id,0)) 

 

edit:  that would probably give you a 1 not 0 like you want.  Using SUM and a CASE statement would probably work.

 

select stats.terminal, SUM(CASE WHEN id IS NULL THEN 0 ELSE 1 END) 

 

 

[Karbono]

select stats.terminal, ifnull(count(id),0) 

 

You need to convert the column to zero before the count is applied, not after.

 

select stats.terminal, count(coalesce(id,0)) 

 

edit:  that would probably give you a 1 not 0 like you want.  Using SUM and a CASE statement would probably work.

 

select stats.terminal, SUM(CASE WHEN id IS NULL THEN 0 ELSE 1 END) 

 

select stats.terminal, SUM(CASE WHEN id IS NULL THEN 0 ELSE 1 END) 
from stats right join (select distinct terminal from stats) as terminais
on stats.terminal=terminais.terminal
where timestamp>'2012-02-15'
group by stats.terminal

 

still returns no terminals with count=0

 

Thanks for trying, though 

[kicken]

Remove your group by and just look at the results:

select stats.terminal, id 
from stats right join (select distinct terminal from stats) as terminais
on stats.terminal=terminais.terminal
where timestamp>'2012-02-15'

 

Do you see the rows for what you want, but with a NULL value for the id column?  If not then that is why they are not counted and you need to fix the query so they  are displayed.  When they are displayed then re-apply the group by.

 

[Karbono]

Remove your group by and just look at the results:

select stats.terminal, id 
from stats right join (select distinct terminal from stats) as terminais
on stats.terminal=terminais.terminal
where timestamp>'2012-02-15'

 

Do you see the rows for what you want, but with a NULL value for the id column?  If not then that is why they are not counted and you need to fix the query so they  are displayed.  When they are displayed then re-apply the group by.

 

 

Yes, I'm aware that's the problem. I just don't know how to fix it. 

[Karbono]

I will accept a solution in PHP 

[kicken]

As mentioned above, the problem is likely because your WHERE condition depends on a column in the table which is null in the case of no join being made.  You could try adding OR timestamp IS NULL to the condition, see if that helps.

 

If not, maybe try moving the timestamp>'2012-02-15' condition from the WHERE clause to your ON clause.

[Karbono]

As mentioned above, the problem is likely because your WHERE condition depends on a column in the table which is null in the case of no join being made.  You could try adding OR timestamp IS NULL to the condition, see if that helps.

 

If not, maybe try moving the timestamp>'2012-02-15' condition from the WHERE clause to your ON clause.

 

I tried several changes. The most significant one was this:

 

mysql> select stats.terminal, count(id )

    -> from stats right join (select distinct terminal from stats) as terminais

    -> on stats.terminal=terminais.terminal and  timestamp>'2012-02-15'

    -> group by terminal;

+-----------------+------------+

| terminal        | count(id ) |

+-----------------+------------+

| NULL            |          0    |

|                        |        21  |

| prog-pc          |          2    |

| testeterminal |        41  |

| ubuntu            |          1  |

+-----------------+------------+

5 rows in set (0.93 sec)

 

Still not there...

[Karbono]

I'll pay a beer to whoever helps me out with this...

[Karbono]

I just tried a small change in the timestamp

 

select Y.terminal, count(Z.id)
from (select distinct terminal from stats) Y
left outer join stats Z on Y.terminal=Z.terminal
where Z.timestamp>'2012-02-15'
group by Y.terminal order by Y.terminal

 

Still doesn't show the lines where the count=0.

[fenway]

None of the timestamp in the sample data you provided are >2012-02-15.

 

Please give us the real query you are using.

[Karbono]

with the sample data you can just use timestamp>'2012-01-16', which will exclude warehouse2. In the end, warehouse2's count should be "0"

[fenway]

Like I suggested a long time ago:

 

select Y.terminal, count(Z.id)
from (select distinct terminal from stats) Y
left outer join stats Z on ( Y.terminal=Z.terminal and Z.timestamp > '2012-01-16'  )
group by Y.terminal order by Y.terminal

[Karbono]

Like I suggested a long time ago:

 

select Y.terminal, count(Z.id)
from (select distinct terminal from stats) Y
left outer join stats Z on ( Y.terminal=Z.terminal and Z.timestamp > '2012-01-16'  )
group by Y.terminal order by Y.terminal

 

Somehow "Sounds like the where clause is operating on a left join'ed field." wasn't enough for my limited understanding of JOIN clauses/english language. It makes sense now.

 

Sorry to have wasted your time.