MySQL - Show 0 when no records are found in a Time Zone [duplicated]

Question

MySQL - Show 0 when no records are found in a Time Zone [duplicated]

Navigation

#1 by (2 votes)

1

Good morning, I would like to know how to count the date field (Ex: 2017-03-12 08:14:12) that is in the specified time zone (Between 08:00:00 and 08:29:00) and until 18:00:00 and 18:29:00 and I SHOW ZERO "0" when I do not find calls, but it only shows me the lines that have calls, if there are no calls I can not show zero, I thought of a LEFT JOIN but it has not worked for me, I'm looking for something like this:

| --- Hour Strip ---- | -Contact- |
| 08: 00: 00 - 08:29:00 | --- 0 ------ |
08: 30: 00 - 08:59:00 | --- 10 ----- | | 09: 00: 00 - 09:29:00 | ---- 2 ------ |
| 09: 30: 00 - 09:59:00 | ---- 0 ------ |
| 10: 00: 00 - 10:29:00 | ---- 5 ------ |
| 10: 29: 00 - 10:59:00 | ---- 0 ------ |
...
...
| 17: 30: 00 - 17:59:00 | ---- 9 ------ |
| 18: 00: 00 - 18:29:00 | ---- 12 ---- |

Deputy UPDATED WITH CONSULTATION: link

Thanks in advance, greetings.

mysql

asked by neojosh2 13.03.2017 в 23:17

source

1 answer

How to call a Selector in Jquery? Why does a random array throw me undefined?

score 2 · Accepted Answer

It does not help to do left join of the table with itself. You need to make a "dummy" table that contains all stripes in hard:

SELECT '08:30 - 08:59' as franja UNION ALL
SELECT '09:30 - 09:59' UNION ALL
SELECT '10:30 - 10:59' UNION ALL
SELECT '11:30 - 11:59' UNION ALL
SELECT '12:30 - 12:59' UNION ALL
SELECT '13:30 - 13:59' UNION ALL
SELECT '14:30 - 14:59' UNION ALL
SELECT '15:30 - 15:59' UNION ALL
SELECT '16:30 - 16:59' UNION ALL
SELECT '17:30 - 17:59' UNION ALL
SELECT '18:30 - 18:59' UNION ALL
SELECT '19:30 - 19:59'

to this table you make a LEFT JOIN with the table of contacts replacing the nulls with zeros:

SELECT franjas.franja, IFNULL(conteo,0) as conteo
FROM franjas LEFT JOIN contactos ON franjas.franja=contactos.franja;

I gave you a rextester example with my solution proposal: link