-1

I am new to SQL, it would be great if you help me. my SQL output is:

| o_date |  c_loc | type | output|\
|3-1-22  |  center 1   |   A     |   52  |\
|3-1-22  |  center 1   |   B     |   23  |\
|3-1-22  |  center 1   |   C     |   19  |\
|3-1-22  |  center 2   |   A     |   34  |\
|3-1-22  |  center 2   |   B     |   21  |\
|3-1-22  |  center 3   |   A     |   59  |\
|3-2-22  |  center 1   |   A     |   45  |\
|3-2-22  |  center 1   |   B     |   29  |

As you can see, there are some dates that some locations had no output. I need to fill this lines with 0. There are 11 centers with 3 types. What I need is the table below:

| o_date |  c_loc | type | output|\
|3-1-22  |  center 1   |   A     |   52  |\
|3-1-22  |  center 1   |   B     |   23  |\
|3-1-22  |  center 1   |   C     |   19  |\
|3-1-22  |  center 2   |   A     |   34  |\
|3-1-22  |  center 2   |   B     |   21  |\
|3-1-22  |  center 2   |   C     |   0   |\
|3-1-22  |  center 3   |   A     |   59  |\
|3-1-22  |  center 3   |   B     |   0   |\
|3-1-22  |  center 3   |   C     |   0   |\
|3-2-22  |  center 1   |   A     |   45  |\
|3-2-22  |  center 1   |   B     |   29  |\
|3-2-22  |  center 1   |   C     |   0   |

and etc.

As far as I understood I need to use { generate_series()}.

Any help would be great.

nbk
  • 45,398
  • 8
  • 30
  • 47
rojin
  • 9
  • 2

1 Answers1

0

No, your requirement does not need generate_series, which is not supported by MySql.

You need a CROSS join of the distinct combinations of o_date and c_loc to the distinct types and a LEFT join to the table:

SELECT t1.o_date, t1.c_loc, t2.type,
       COALESCE(t.output, 0) output
FROM (SELECT DISTINCT o_date, c_loc FROM tablename) t1
CROSS JOIN (SELECT DISTINCT type FROM tablename) t2
LEFT JOIN tablename t ON t.o_date = t1.o_date AND t.c_loc = t1.c_loc AND t.type = t2.type
ORDER BY t1.o_date, t1.c_loc, t2.type;

See the demo.

forpas
  • 160,666
  • 10
  • 38
  • 76