TSQL "where ... group by ..." issue that needs solution like "having ..."-CodePudding

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.

CodePudding user response：

What you want will require a complete list of locations, then a left-outer join using that table and your calculated values, and IsNull (for tsql) to ensure you see the 0s you expect. You can do this with some CTEs, which I find valuable for clarity during development, or you can work on "putting it all together" in a more traditional SELECT...FROM... statement. The CTE approach might look like this:

WITH loc AS (
  SELECT DISTINCT LocationID 
  FROM location_table
), summary_data as (
  SELECT LocationID, SUM(amount) AS location_sum 
  FROM master-table 
  GROUP BY LocationID
)
SELECT loc.LocationID, IsNull(location_sum,0) AS location_sum
FROM loc 
LEFT OUTER JOIN summary_data ON loc.LocationID = summary_data.LocationID

See if that gets you a step or two closer to the results you're looking for.

CodePudding user response：

The version using T-SQL without CTEs would be:

SELECT l.location ,
       ISNULL(m.location_sum, 0) as location_sum
FROM master-table l
LEFT JOIN (
           SELECT location, 
                  SUM(amount) as location_sum
           FROM master-table
           WHERE (time ... )
           GROUP BY location
          ) m ON l.location = m.location

This assumes that you still have your initial UNION in place that ensures that master-table has all possible locations included.

CodePudding user response：

It is the where clause that excludes some locations. To ensure you retain every location you could introduce "conditional aggregation" instead of using the where clause: e.g.

select location, sum(case when (time...) then amount else 0 end) as location_sum
from master-table
group by location

i.e. instead of excluding some rows from the result, place the conditions inside the sum function that equate to the conditions you would have used in the where clause. If those conditions are true, then it will aggregate the amount, but if the conditions evaluate to false then 0 is summed, but the location is retained in the result.

CodePudding user response：

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