I have a table Category
which has a few missing records for a few months. The table looks like this
Category Month Amount
Opt January 12.00
Opt February 0.00
Opt March -1042000.00
Opt April 0.00
Opt May 0.00
Opt June 0.00
Opt July -782663.00
Opt November 0.00
Opt December 0.00
This table has missing rows for a few months. I would like to query and show this data as well.
So I created a month table
MonthNameLong MonthNameShort
January Jan
February Feb
March Mar
April Apr
May May
June Jun
July Jul
August Aug
September Sep
October Oct
November Nov
December Dec
So that I can do a right join on this and get the missing details as well. The query I wrote is
select s.Category, m.MonthNameLong, coalesce(s.Amount, 0.0) as Amount
from Category s
RIGHT JOIN Month m
ON s.Month = m.MonthNameLong
And the result I get is
Category MonthNameLong Amount
Opt January 12.00
Opt February 0.00
Opt March -1042000.00
Opt April 0.00
Opt May 0.00
Opt June 0.00
Opt July -782663.00
NULL August 0.00
NULL September 0.00
NULL October 0.00
Opt November 0.00
Opt December 0.00
But instead of NULL category is there a way I can get Opt as the category.
I have attached the fiddle here https://www.db-fiddle.com/f/7sycoGZ4sYxNzdYeTvJauC/0
This is very similar to SQL join query to show rows with non-existent rows in one table question but that has all the data in the table so there isn't missing rows.