Case statement group by

Question

I want to find NET_AMT for a year.. NET_AMT is SUM(SAL_AMT)/SUM(INC_AMT).. I have the below query where I get 12 values for NET_AMT, since I am grouping by DATE_KEY.. But I have to get just one record for 1 year..

SELECT 
DIM_NUM, 
SN_NUM,
REL_NUM,
SUM(INS_AMT) AS INS,
CASE WHEN DATE_KEY BETWEEN 200801 AND 200812 THEN 
(CASE WHEN SUM(INC_AMT) <> 0 THEN SUM(SAL_AMT)/SUM(INC_AMT)END) END AS NET_AMT
FROM 
Table_A
GROUP BY 
DIM_NUM, 
SN_NUM,
REL_NUM,
DATE_KEY;

Answer 1

This is your query:

SELECT DIM_NUM, SN_NUM, REL_NUM, SUM(INS_AMT) AS INS,
       (CASE WHEN DATE_KEY BETWEEN 200801 AND 200812
             THEN (CASE WHEN SUM(INC_AMT) <> 0 THEN SUM(SAL_AMT)/SUM(INC_AMT) END)
        END) AS NET_AMT
FROM Table_A
GROUP BY DIM_NUM, SN_NUM, REL_NUM, DATE_KEY;

Move the condition to a where clause and remove date_key from the group by:

SELECT DIM_NUM, SN_NUM, REL_NUM, SUM(INS_AMT) AS INS,
       (CASE WHEN SUM(INC_AMT) <> 0 THEN SUM(SAL_AMT)/SUM(INC_AMT)
        END) AS NET_AMT
FROM Table_A
WHERE DATE_KEY BETWEEN 200801 AND 200812
GROUP BY DIM_NUM, SN_NUM, REL_NUM;

EDIT:

It really would be easier if people asked the right question the first time. Assuming that DATE_KEY is a number:

SELECT DIM_NUM, SN_NUM, REL_NUM, SUM(INS_AMT) AS INS,
       LEFT(CAST(DATE_KEY as VARCHAR(255)), 4) as Year,
       (CASE WHEN SUM(INC_AMT) <> 0 THEN SUM(SAL_AMT)/SUM(INC_AMT)
        END) AS NET_AMT
FROM Table_A
GROUP BY DIM_NUM, SN_NUM, REL_NUM, LEFT(CAST(DATE_KEY as VARCHAR(255)), 4)

Answer 2

More like this:

SELECT 
  DIM_NUM, 
  SN_NUM,
  REL_NUM,
  SUM(INS_AMT) AS INS,
  SUM(CASE WHEN DATE_KEY BETWEEN BETWEEN 200801 AND 200812 THEN SAL_AMT ELSE 0 END) /
  SUM(CASE WHEN DATE_KEY BETWEEN BETWEEN 200801 AND 200812 THEN INC_AMT ELSE 0 END) AS NET_AMT
FROM Table_A
GROUP BY DIM_NUM, SN_NUM, REL_NUM, DATE_KEY;