SELECT a.system, a.subsystem, COUNT(a.system) as numprojects, SUM(total_cost) as Total_Cost FROM systems a inner join rtp_projects b on a.system = b.system and a.subsystem = b.subsystem where b.Basecase = 1 group by a.system, a.subsystem order by a.system, a.subsystemNow I need to add another column that shows the sum of the Total_cost of the projects where Category = 'Capacity', something like this
SELECT a.system, a.subsystem, COUNT(a.system) as numprojects, SUM(total_cost) as Total_Cost, SUM(Total_cost where Category = 'Capacity') FROM systems a inner join rtp_projects b on a.system = b.system and a.subsystem = b.subsystem where b.Basecase = 1 group by a.system, a.subsystem order by a.system, a.subsystembut I can't figure out the syntax for this additional column. Can someone help?