CODEWITHSUNDEEP
mysqlsql
d586
d586

Reputation: 257

Query to put dummy data in missing rows

I have a MYSQL table with missing rows and want a query so that each week 'GROUP' has same number of rows.

In this example I need each week I have 3 days and I want to have the same number of days/rows in each week output for further processing:

week,day,value
1,1,101
1,2,102
1,3,103
2,1,201
2,2,202
3,1,301
3,2,302

I want to produce something like this - each 'week' has the same number of rows. 

week,day,value
1,1,101
1,2,102
1,3,103
2,1,201
2,2,202
2,3,NULL
3,1,301
3,2,302
3,3,NULL

Either fill 'value' with zeros or NULLs or some default value -even if the missing day was set to NULL or something would do. The numbers of rows/days will always be the same (3 in this case) but there may 1 or more days missing from any week.

I am sort of stumped how to do it. Would a LEFT JOIN to a table with three rows 1,2,3 be a possible way?

The real tables are rather big and the manual creation of a cross reference table containing all (week, day) is not practicable.

Thanks!

Create Statement

CREATE TABLE `new1` (
  `week` int(11) DEFAULT NULL,
  `day` int(11) DEFAULT NULL,
  `value` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Insert Statement

INSERT INTO test.new1 
(week, day, value) 
VALUES 
(1,1,101),
(1,2,102),
(1,3,103),
(2, 1, 201),
( 2,2, 202),
( 3, 1, 301),
( 3, 2, 302);

Upvotes: 0

Views: 1102

Answers (1)

Hart CO
Hart CO

Reputation: 34784

You could create a list of all possible week/day combinations, then left join your existing table to pull over the value:

SELECT a.*,b.value
FROM (SELECT *
      FROM (SELECT DISTINCT week FROM Table1) wk
          ,(SELECT DISTINCT day FROM Table1) dy
     )a
LEFT JOIN Table1 b
  ON a.week = b.`week`
  AND a.day = b.`day`

Demo: SQL Fiddle

To just add the filler rows:

INSERT INTO Table1 (`week`, `day`, `value`)
    SELECT a.*,b.value
    FROM (SELECT *
          FROM (SELECT DISTINCT week FROM Table1) wk
              ,(SELECT DISTINCT day FROM Table1) dy
         )a
    LEFT JOIN Table1 b
      ON a.week = b.`week`
      AND a.day = b.`day`
    WHERE b.week IS NULL

Upvotes: 2

Related Questions