的MySQL如何计算和点心condtionally

Question

我的MySQL的加入两个表后的结果如下： -

 ID    || Category  ||  Cost  || 
========================||==================||==================|| 
AMWP/ABC/2016-17/1  ||  1   ||  123.88  || 
AMWP/CDF/2016-17/2  ||  2   ||  222.99  || 
AMWP/GHI/2016-17/3  ||  3   ||  133.90  || 
AMWP/ABC/2017-18/1  ||  1   ||  100.10  || 
AMWP/CDF/2017-18/2  ||  2   ||  200.20  || 
AMWP/GHI/2017-18/3  ||  3   ||  100.00  || 

我想提取摘要按CATERGORY每个CATERGORY的计数和总成本只其中ID具有像ABC/CDF/GHI和财政年度之一一样2016-17/2017-18

Answer 1

我明白了我想要的方式。在以下SQL中，只要条件为True，我就使用Sum而不是Count至Add 1 to TotWks。此外，只要条件为True，我只需Adding即Cost Field至TotCost。

SELECT 
Category, 
SUM(if(ID LIKE '%2017-18%' AND ID like ‘%/ABC/%, 1, 0)) AS `TotWks`, 
SUM(if(ID LIKE '%2017-18%' AND ID like ‘%/ABC/%, `amwplist`.`Cost`, 0)) AS `TotCost` 
FROM <main_Table> 
LEFT OUTER JOIN <join_Table> ON <main_Table.ID> = <join_Table. ID> 
GROUP BY <main_Table.ID> 

如果我省略了GROUP BY条款，那么我会TotWks和TotCost下，共展宏图。

感谢您的帮助。

Answer 2

select category, sum(Cost) 
     from your_tables 
     where 
     (ID like ‘%/ABC/% or ID like ‘%/CDF%/’ or ID like ‘%/GHI/%’) 
     and 
     (ID like ‘%/2016-17/%’ or ID like ‘%/2017-18/%’) 
     and <joining condition> 
     group by category;