[Solved] Whats wrong with my SQL?

by


Your last query

   SELECT 
         0,
         department,
         department_name,
         '',
          'NP' as acc_code,
         'NET PROFIT/(LOSS)' as acc_name,1,
         SUM((CASE WHEN acc_code="CS" OR acc_code="EX" THEN -1*yr_balance ELSE yr_balance END)),
         SUM((CASE WHEN acc_code="CS" OR acc_code="EX" THEN -1*ly_balance ELSE ly_balance END)),
         SUM((CASE WHEN acc_code="CS" OR acc_code="EX"  THEN -1*mn_balance ELSE mn_balance END)),
         SUM((CASE WHEN acc_code="CS" OR acc_code="EX"  THEN -1*lm_balance ELSE lm_balance END)),
         'CAT_NETPROFIT' as rsl_type,
         1 
   FROM 
     #tmp_rsl WHERE rsl_type="CAT_HEAD"

is the problem. You use columns like ‘department’ without any aggregate and then you have columns computed by an aggregate (like sum). You can not mix those colums. Either you have no aggregates, all aggregates or the columns without any aggregate must be part of your grouping, like this:

   SELECT 
         0,
         department,
         department_name,
         '',
          'NP' as acc_code,
         'NET PROFIT/(LOSS)' as acc_name,1,
         SUM((CASE WHEN acc_code="CS" OR acc_code="EX" THEN -1*yr_balance ELSE yr_balance END)),
         SUM((CASE WHEN acc_code="CS" OR acc_code="EX" THEN -1*ly_balance ELSE ly_balance END)),
         SUM((CASE WHEN acc_code="CS" OR acc_code="EX"  THEN -1*mn_balance ELSE mn_balance END)),
         SUM((CASE WHEN acc_code="CS" OR acc_code="EX"  THEN -1*lm_balance ELSE lm_balance END)),
         'CAT_NETPROFIT' as rsl_type,
         1 
   FROM 
     #tmp_rsl WHERE rsl_type="CAT_HEAD"
   GROUP BY department, department_name

0

solved Whats wrong with my SQL?