TSQL “where … group by …” issue that needs solution like “having …”

I have 3 sub-tables of different formats joined together with unions if this affects anything into full-table. There I have columns “location”, “amount” and “time”. Then to keep generality for my later needs I union full-table with location-table that has all possible “location” values and other fields are null into master-table.

I query master-table,

select location, sum(amount)
from master-table
where (time...)
group by location

However some “location” values are dropped because sum(amount) is 0 for those “location”s but I really want to have full list of those “location”s for my further steps.

Alternative would be to use HAVING clause but from what I understand HAVING is impossible here because i filter on “time” while grouping on “location” and I would need to add “time” in grouping which destroys the purpose. Keep in mind that the goal here is to get sum(amount) in each “location”

select location, sum(amount)
from master-table
group by location, time
having (time...)

To view the output: with the first code I get

loc1, 5
loc3, 10
loc6, 1

but I want to get

loc1, 5
loc2, 0
loc3, 10
loc4, 0
loc5, 0
loc6, 1

Any suggestions on what can be done with this structure of master-table? Alternative solution to which I have no idea how to code would be to add numbers from the first query result to location-table (as a query, not actual table) with the final result query that I’ve posted above.

Answer

I can think of 2 options:

You could move the WHERE to a CASE WHEN construction:

-- Option 1
select 
   location, 
   sum(CASE WHEN time <'16:00' THEN amount ELSE 0 END) 
from master_table
group by location

Or you could JOIN with the possible values of location (which is my first ever RIGHT JOIN in a very long time 😉):

-- Option 2
select 
   x.location, 
   sum(CASE WHEN m.time <'16:00' THEN m.amount ELSE 0 END) 
from master_table m
right join (select distinct location from master_table) x ON x.location = m.location
group by x.location

see: DBFIDDLE