mysql - 如何根据A列的数据将B列的SQL搜索结果汇总为sum？

Question

我创建了一个 SQL 公式来搜索在特定机器的信号状态上花费的时间:

SELECT 'Core' as Core, Z.EventName, ISNULL(DIFF,0) AS DIFF 
FROM (select distinct MacID, EventName from dbo.tblMachine a  
join (SELECT DISTINCT EVENTNAME FROM dbo.tblEvtDur) 
b on b.EventName <> '' and MacID in ('A06','A07', 'A08', 'A09', 'B01','B05','B09', 'B10', 'B11', 'C04', 'C08', 'C09') ) 
z LEFT JOIN (SELECT A.MacID, A.EventName, SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) 
as diff,round(SUM(DATEDIFF(SECOND, A.STARTdt, A.eNDdt)) / cast(aVG(Tdiff) 
as decimal(30,8)),4) * 100 AS PER FROM dbo.tblEvtDur  A 
LEFT JOIN ( SELECT macid, SUM(DATEDIFF(SECOND, STARTdt, eNDdt)) as Tdiff 
FROM  dbo.tblEvtDur  WHERE DayID between '20180401' and '20180430'  
GROUP BY MacID ) B ON  A.MacID = B.MacID 
WHERE DayID between '20180401' and '20180430'  
AND A.MacID in
 ('A06','A07', 'A08', 'A09', 'B01','B05','B09', 'B10', 'B11', 'C04', 'C08', 'C09') 
group by A.MacID, A.EventName) a 
ON A.EVENTNAME = Z.EVENTNAME  and Z.MacID  = a.MacID order by Z.MacID, Z.EventName

我得到的结果如下所示:

现在我想让 SQL 显示这些特定机器的 EventName BLK、MDL、MTL、OFF、RPR 和 RUN 的总和。这可能吗？

编辑:

如果我尝试使用 group by 子句添加总和，它在当前 SQL 上不起作用。

所以我不得不将其重构为:

select 'Core' as Core, EventName, sum(DATEDIFF(second, startdt, EndDT)) as DIFF 
from dbo.tblEvtDur

where MacID in ('A06','A07','A08','A09','B01','B05','B09','B10','B11','C04','C08','C09')

and DayID between '20180531' and '20180531'

group by eventname

但根据日期的不同，某些信号不会出现，因此不会列在结果表中。

我应该添加什么来告诉 SQL 将 EventName BLK 显示为 0？

编辑 2:

到目前为止尝试了 Dan 关于使用右连接的建议:

select 'Core' as [Core], a.EventName, isnull((sum(DATEDIFF(second, startdt, EndDT))),0) as DIFF 
from dbo.tblEvtDur a
right join (select distinct eventname from dbo.tblEvtDur where EventName in     
('BLK', 'MDL', 'MTL', 'OFF', 'RPR', 'RUN')) b 
on a.EventName = b.EventName
where MacID in ('A06','A07', 'A08', 'A09', 'B01','B05',
'B09', 'B10', 'B11', 'C04', 'C08', 'C09')
and DayID between '20180531' and '20180531'
group by a.eventname

同样，结果相同，但花费的时间明显更长。

Answer 1

将聚合函数(sum、count、avg...)与分组依据结合使用。

 select count(*) from (output of your query) group by EventName;