How to create a view that is summing a certain column for 3 different filtered items to create the 3-CodePudding

I need to create a View from a table that sums the total from a certain column (DT_Units) and creates 3 new columns with the correct sum for Total_Hourly_Units, Total_Standby_Units and Total_Footage_Units. How do I combine this query to make the 3 new columns on the same view with the correct value for each new column. This is what I have.

select DT_Company,CollarYear_D, DT_Month ,Sum(convert(float,DT_Units)) as Total_Hourly_Units
from dbo.DRILLTRACKING
where DT_Category = 'Hourly' 
group by DT_Company, CollarYear_D, DT_Month

select DT_Company,CollarYear_D, DT_Month,Sum(convert(float,DT_Units)) as Total_Standby_Units
from 
dbo.DRILLTRACKING
where DT_Category= 'Standby'
group by DT_Company, CollarYear_D, DT_Month

select DT_Company,CollarYear_D, DT_Month,Sum(convert(float,DT_Units)) as Total_Footage_Units
from 
dbo.DRILLTRACKING
where DT_Category= 'Footage'
group by DT_Company, CollarYear_D, DT_Month

CodePudding user response：

You want a conditional aggregation. Most DBMSes will understand the query

select DT_Company,CollarYear_D, DT_Month ,
    Sum(case when DT_Category = 'Hourly' then convert(float,DT_Units) end) as Total_Hourly_Units,
    Sum(case when DT_Category = 'Standby' then convert(float,DT_Units) end) as Total_Standby_Units,
    Sum(case when DT_Category = 'Footage' then convert(float,DT_Units) end) as Total_Footage_Units,
from dbo.DRILLTRACKING
where DT_Category = in ('Hourly', 'Standby', 'Footage')
group by DT_Company, CollarYear_D, DT_Month