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.
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