mysql fill missing values with 0

Question

I have a mysql query that sometimes results in missing values. For my dashboard I'd like to fill those values, but would prefer to avoid build dummy tables if I can.

query:

SELECT COUNT(Comms_Timestamp) as call_count,DAYOFWEEK(Comms_Timestamp) as bucket 
FROM tblTest GROUP BY bucket;

results in

+------------+--------+
| call_count | bucket |
+------------+--------+
|          4 |      1 |
|          7 |      2 |
|          7 |      3 |
|          1 |      5 |
|          6 |      6 |
|          1 |      7 |
+------------+--------+

In the above example you can see bucket 4 is missing. I consider the method where the join is to a select union array, however since both fields are aggregates, I'm not sure how to go about it.

test data is

+---------------------+
| Comms_Timestamp     |
+---------------------+
| 2018-12-24 06:04:05 |
| 2018-12-24 12:18:39 |
| 2018-12-21 04:24:31 |
| 2018-12-21 08:32:44 |
| 2018-12-30 01:41:06 |
| 2018-12-30 01:53:00 |
| 2018-12-30 01:53:39 |
| 2018-12-30 02:00:01 |
| 2018-12-17 15:55:03 |
| 2018-12-17 16:04:12 |
| 2018-12-17 16:05:41 |
| 2018-12-17 16:07:43 |
| 2018-12-17 16:10:25 |
| 2018-12-18 14:03:22 |
| 2018-12-18 14:03:29 |
| 2018-12-18 14:10:19 |
| 2018-12-18 14:10:29 |
| 2018-12-18 14:10:31 |
| 2018-12-18 14:10:47 |
| 2018-12-18 14:10:55 |
| 2018-12-20 08:21:07 |
| 2018-12-28 11:03:59 |
| 2018-12-28 12:06:40 |
| 2018-12-28 12:15:01 |
| 2018-12-28 14:29:24 |
| 2019-01-05 13:33:43 |
+---------------------+

Can u post few more things like the structure of the two tables with some data in it? This much info is inadequate to suggest properly. — Bush
– Bush, Commented Feb 8, 2019 at 5:52
@RaviGaudani I'm specifically trying to avoid building empty tables.. — baradhili
– baradhili, Commented Feb 8, 2019 at 7:24

Igor Ilic · Accepted Answer · 2019-02-08 08:18:14Z

1

Since you are using mysql and don't have access to the seq_ option, here is an alternative way:

SELECT A.x AS bucket, IF(ISNULL(COUNT(t2.Comms_Timestamp)), 0, COUNT(t2.Comms_Timestamp)) AS call_count FROM
(select 1 x union select 2 union select 3 union select 4 union select 5 union select 6 union select 7) AS A
LEFT JOIN tblTest AS t2 ON DAYOFWEEK(t2.Comms_Timestamp) = A.x
GROUP BY bucket
ORDER BY bucket;

It may not be the prettiest option but will do what you need.

Here is a db fiddel link: db<>fiddle

answered Feb 8, 2019 at 8:18

Igor Ilic

1,3681 gold badge11 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

danblack · Accepted Answer · 2019-02-08 06:01:43Z

0

If you are using MariaDB there is their Sequence Storage Engine

There is no create needed for this table, however the maximum value must be known.

select version();

| version()                                   |
| :------------------------------------------ |
| 10.3.11-MariaDB-1:10.3.11+maria~stretch-log |

create table bob (a int)

✓

insert into bob values (4),(2)

✓

select * from seq_1_to_5

| seq |
| --: |
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |

SELECT s.seq, bob.a
FROM seq_1_to_5 s 
LEFT JOIN bob 
  ON bob.a = s.seq
ORDER BY s.seq

seq |    a
--: | ---:
  1 | null
  2 |    2
  3 | null
  4 |    4
  5 | null

db<>fiddle here

answered Feb 8, 2019 at 6:01

danblack

14.9k2 gold badges27 silver badges49 bronze badges

1 Comment

baradhili Over a year ago

unfortunately I'm running mysql for this one

Shah Muhammad Talha Tahir · Accepted Answer · 2019-02-08 07:02:26Z

0

You can use IFNULL() function in MYSQL:

SELECT IFNULL(COUNT(C.Comms_Timestamp),0) as call_count,IFNULL(DAYOFWEEK(C.Comms_Timestamp),0) as bucket 
FROM tblCommunication as C LEFT JOIN tblCareTeam as CT on C.id_Case = CT.id_Case 
GROUP BY CT.id_Site,bucket 
HAVING CT.id_Site=8;

answered Feb 8, 2019 at 7:02

Shah Muhammad Talha Tahir

3561 gold badge3 silver badges11 bronze badges

1 Comment

baradhili Over a year ago

Sorry that defintly doesn't do anything

Collectives™ on Stack Overflow

mysql fill missing values with 0

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related