MySQL SUM over CASE语句

本文介绍了MySQL SUM over CASE语句的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

有人可以帮助我在下面的SQL中获得A_VAL的总和

Could someone please help me get the SUM of A_VAL in the below SQL

SELECT CASE WHEN COUNT(t.A_INS_NAM) = 10 THEN 1 WHEN COUNT(t.A_INS_NAM) = 20 THEN 2 WHEN COUNT(t.A_INS_NAM) BETWEEN 30 AND 50 THEN 3 WHEN COUNT(t.A_INS_NAM) > 50 THEN 5 ELSE 'some value' END AS A_VAL FROM TABLE1 t JOIN IWD t1 ON(t.A_INS_NAM = t1.A_INS_NAM) JOIN TIM t2 ON(t1.IWD = t2.IM_ID) JOIN TWS t3 ON(t2.IM_ID = t3.T_ID ) WHERE t3.TN = 'abced' AND t.A_DATE BETWEEN '2014-01-01' AND '2015-05-01' AND t.A_INS_NAM NOT LIKE '%pk%'

推荐答案

您可以使用子选择来计算总和，请注意，您使用的是不带分组的聚集函数，结果将是单行

You can use a sub select to calculate sum, note you are using an aggregate function without group by it will result as a single row

SELECT SUM(A_VAL) FROM ( SELECT CASE WHEN COUNT(t.A_INS_NAM) = 10 THEN 1 WHEN COUNT(t.A_INS_NAM) = 20 THEN 2 WHEN COUNT(t.A_INS_NAM) BETWEEN 30 AND 50 THEN 3 WHEN COUNT(t.A_INS_NAM) > 50 THEN 5 ELSE 'some value' END AS A_VAL FROM TABLE1 t JOIN IWD t1 ON(t.A_INS_NAM = t1.A_INS_NAM) JOIN TIM t2 ON(t1.IWD = t2.IM_ID) JOIN TWS t3 ON(t2.IM_ID = t3.T_ID ) WHERE t3.TN = 'abced' AND t.A_DATE BETWEEN '2014-01-01' AND '2015-05-01' AND t.A_INS_NAM NOT LIKE '%pk%' ) a