sql - Sum of 2 selects statement in sql with different where Clause

Question

I am trying to get the sum of 2 columns in one table but with different where the condition, the only difference is the amount per department is calculated based on 17% Margin. The Result should be the total revenue grouped by Event Name and Event ID.

for a sql Report, I have written 2 sql statements with different conditions and got the correct value for 2 columns but separately, i have summed both in a way but it was for one event.

SELECT EVT_ID, Event_Desc, Sum(Order_Total) as Total + (Select SUm(Order_Total *0.17) as Total from Orders Join Events EM On OrD.EVT_ID = EV.EVENTS_ID 
where EVT_START_DATE between '2019-01-01' and '2019-01-31' Order_Department = 'FAB'  )
From Orders Join Events EM On OrD.EVT_ID = EV.EVENTS_ID 
where EVT_START_DATE between '2019-01-01' and '2019-01-31' Order_Department <> 'FAB'  
Group by EVT_ID, Event_Desc

标签： sqlsql-server-2008

Answer 1

select EVT_ID, Event_Desc, sum(Total)as Total 
from 
(
SELECT EVT_ID, Event_Desc, Sum(Order_Total) as Total 
 From Orders 
 Join Events EM On OrD.EVT_ID = EV.EVENTS_ID 
 where EVT_START_DATE between '2019-01-01' and '2019-01-31' and Order_Department <> 'FAB'
Group by EVT_ID, Event_Desc 
 union
Select EVT_ID, Event_Desc, SUm(Order_Total *0.17) as Total 
from Orders 
Join Events EM On OrD.EVT_ID = EV.EVENTS_ID 
 where EVT_START_DATE between '2019-01-01' and '2019-01-31' and Order_Department = 'FAB' )  tbl   
 Group by EVT_ID, Event_Desc

或者

 SELECT EVT_ID, Event_Desc, Sum(case when Order_Department = 'FAB' then Order_Total else Order_Total *0.17 end ) as Total 
 From Orders 
 Join Events EM On OrD.EVT_ID = EV.EVENTS_ID 
 where EVT_START_DATE between '2019-01-01' and '2019-01-31'  
Group by EVT_ID, Event_Desc