MySQL SUM over CASE语句 [英] MySQL SUM over CASE statement

问题描述

有人可以帮助我在下面的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

这篇关于MySQL SUM over CASE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！