CODEWITHSUNDEEP
mysql
User12345
User12345

Reputation: 325

MySQL SUM over CASE statement

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%'

Upvotes: 0

Views: 56

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64476

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

Upvotes: 1

Related Questions