How to sum multiple field in one field in sql query?

user_10877817 0
Viewed 137

I got much code in the input, this code has a parent 

SELECT [fromAccCode]= 
CASE WHEN [fromAccCode] IN (201010, 201020, 201442, 201521, 201611,201612,209050,
209060,209090,209110,213040,213163,213165,213166,213190) THEN r1
WHEN [fromAccCode] IN (201040, 201048, 201050, 201090, 201120, 201150
,201155) THEN r2
WHEN [fromAccCode] IN (201443,206020,207010,207040,207100,207130,209612
) THEN r3

and I set input with this code 

SELECT [fromAccCode] FROM [dbo].[source_table] WHERE ','+'201010, 
201020,259861,754265,213040,213163,213165,201040,
201048, 201050,201443,206020'+',' like '%,'+cast([fromAccCode] 
AS varchar(10))+',%'

I want you have 3 records 1,2,3 form sum of values(r1), sum of values(r2) and sum of values(r3) and this fields have values

sql-server sql
2 Answers

Originally I assumed you could split the WHEN's then SUM them.

SELECT 
SUM(CASE WHEN [fromAccCode] IN (201010, 201020, 201442, 201521, 201611, 201612, 209050, 209060, 209090, 209110, 213040, 213163, 213165, 213166, 213190) THEN r1 END) AS TotalR1,
SUM(CASE WHEN [fromAccCode] IN (201040, 201048, 201050, 201090, 201120, 201150, 201155) THEN r2 END) AS TotalR2,
SUM(CASE WHEN [fromAccCode] IN (201443, 206020, 207010, 207040, 207100, 207130, 209612) THEN r3 END) AS TotalR3
FROM [dbo].[source_table]
WHERE [fromAccCode] IN (
  201010, 201020, 201442, 201521, 201611, 201612, 209050, 209060, 209090, 209110, 213040, 213163, 213165, 213166, 213190,
  201040, 201048, 201050, 201090, 201120, 201150, 201155,
  201443, 206020, 207010, 207040, 207100, 207130, 209612);

But it seems that you were looking for something like this monstrosity.

SELECT src.fromAccCode,
(CASE 
 WHEN ref.TypeR = 1 THEN src.r1
 WHEN ref.TypeR = 2 THEN src.r2
 WHEN ref.TypeR = 3 THEN src.r3 
 END) AS rx,
SUM([value]) as SumValues
FROM [dbo].[source_table] as src
JOIN (SELECT DISTINCT fromAccCode, TypeR FROM (VALUES 
 (201010,1), (201020,1), (201442,1), (201521,1), (201611,1), (201612,1), (209050,1), (209060,1), (209090,1), (209110,1), 
 (213040,1), (213163,1), (213165,1), (213166,1), (213190,1),
 (201040,2), (201048,2), (201050,2), (201090,2), (201120,2), (201150,2), (201155,2),
 (201443,3), (206020,3), (207010,3), (207040,3), (207100,3), (207130,3), (209612,3)
) val (fromAccCode, TypeR)) AS ref ON ref.fromAccCode = src.fromAccCode
GROUP BY src.fromAccCode,
(CASE 
 WHEN ref.TypeR = 1 THEN src.r1
 WHEN ref.TypeR = 2 THEN src.r2
 WHEN ref.TypeR = 3 THEN src.r3 
 END);

Btw, about that LIKE criteria.
Using a CONCAT instead of cast to a varchar also works.

WHERE ',' + '201010,201020,259861,754265,213040,213163,213165,201040,201048,201050,201443,206020' + ',' 
      LIKE CONCAT('%,', [fromAccCode], ',%')
user_40034190

first : add one field to set number of case 

  select  [fromAccCode], --your code
field_name=  --add this code
          CASE 
      WHEN [fromAccCode] IN (201010,201020,201442,201521,201611,201612,209050,209060,209090,209110,213040,213163,213165,213166,213190) THEN 'r1         
      WHEN [fromAccCode] IN (201040,201048,201050,201090,201120,201150,201155) THEN r2
      WHEN [fromAccCode] IN (201443,206020,207010,207040,207100,207130,209612) THEN r3     
               ELSE null
          END

You do not need this code snippet : 

WHERE ',' + '201010,201020,259861,754265,213040,213163,213165,201040,201048,201050,201443,206020' + ',' 
  LIKE CONCAT('%,', [fromAccCode], ',%')

Because you have the fromAccCode value

second : 

 select [fromAccCode ]
  ,[field_name]
  ,sum([value])
   from #result_table  group by [fromAccCode ]
  ,[field_name]
  ,[value]
user_67211200
Related