combining tables so that unrecorded data become zero instead of Null

Question

I have a data set for Users per minute over an hour that only includes non zero entries, I would like to update the table so that it includes all minuets. Currently my first would like like this if I had inserted it instead of imported it:

`

 CREATE TABLE table1 (minute INT NOT NULL, users INT NOT NULL);
     INSERT INTO table1 (minute, users) VALUES (32,1);
     INSERT INTO table1 (minute, users) VALUES (40,1);
     INSERT INTO table1 (minute, users) VALUES (41,1);
     INSERT INTO table1 (minute, users) VALUES (51,1);
     INSERT INTO table1 (minute, users) VALUES (52,1);

`

I would like the 55 other minutes in an hour to show up along with the a 0 users. the range is (0-59)

to help that I created a second table

`

 CREATE TABLE m_fix
    (minute INT NOT NULL, users INT NOT NULL); 
    INSERT INTO m_fix (minute, users) VALUES (0,0);
    INSERT INTO m_fix (minute, users) VALUES (1,0);
    INSERT INTO m_fix (minute, users) VALUES (2,0);
    INSERT INTO m_fix (minute, users) VALUES (3,0);......

`

I was able to do a a select join to display the information I wanted with this code:

`

SELECT
     m_fix.minute,
     Case
     When table1.users IS NULL THEN m_fix.users
     ELSE table1.users
     END AS users
     FROM m_fix
     LEFT JOIN table1 ON m_fix.minute=table1.minute;

`

But I want to update the my table1 instead of running a query since I am eventual going to export this data elsewhere. to do that I made this code:

`

INSERT INTO dbo.table1
 SELECT dbo.fix.minute, dbo.fix.users
 FROM dbo.fix
 LEFT JOIN dbo.table1
 ON dbo.fix.minute=dbo.table1.minute
 Where dbo.fix.minute!= dbo.table1.minute;
 Select * From dbo.table1;

`

and it just have me my old table 1 with 5 rows

This is only my test case, I have days more data to deal with so I would like one commanded that I can run on each new table I import that adds all the missing minuets with user = 0

Ok, I read this a few times and I'm not clear on the requirements still. You want to replace the `NULL` values with `0`? I am not sure how you would have nulls in the table where the column is defined as not null. Is the original table structure different somehow? Anyway, the solution seems to be to give the column a default value (you can use a case statement, if needed) but again I'm not real clear on what you are asking. — Jacob H, May 01 '18 at 16:58

score 0 · Answer 1 · answered May 01 '18 at 17:02

0

Since NULL does not equal NULL, your WHERE clause is inadvertently filtering the data you are trying to retrieve.

Change this section:

 SELECT dbo.fix.minute, dbo.fix.users
 FROM dbo.fix
 LEFT JOIN dbo.table1
 ON dbo.fix.minute=dbo.table1.minute
 Where dbo.fix.minute!= dbo.table1.minute;

To this:

 SELECT dbo.fix.minute, dbo.fix.users
 FROM dbo.fix
 LEFT JOIN dbo.table1
 ON dbo.fix.minute=dbo.table1.minute
 Where dbo.table1.minute IS NULL;

answered May 01 '18 at 17:02

AHiggins

7,029
6
36
54

Further reading: https://stackoverflow.com/questions/1833949/why-is-null-not-equal-to-null-false – AHiggins May 01 '18 at 17:03

score 0 · Answer 2 · answered May 01 '18 at 17:09

0

You want to insert those minutes which don't exist in your table, which directly translates to:

INSERT INTO dbo.table1
 SELECT fix.minute, users
 FROM dbo.fix
 WHERE NOT EXISTS
  (
    SELECT * 
    FROM dbo.table1
    WHERE dbo.fix.minute=dbo.table1.minute -- same minute
  )

And you Select can be simplified using COALESCE when you want them to be zero anyway:

SELECT
   m_fix.minute,
   COALESCE(table1.users, 0) AS users
FROM m_fix
LEFT JOIN table1
ON m_fix.minute=table1.minute;

answered May 01 '18 at 17:09

dnoeth

59,503
4
39
56

Thank you, that first insert into part fixed the issue. I don't yet understand what coalesce really does but I will play around it it. – Jane doe May 01 '18 at 21:04
`COALESCE(table1.users, 0)` is a shortcut for `Case When table1.users IS NOT NULL THEN table1.users ELSE 0 END` – dnoeth May 01 '18 at 22:24

combining tables so that unrecorded data become zero instead of Null

2 Answers2