asked
Having problem with Aggregation
Hello All, I am trying to solve a problem.I have a table with composite key (col1+col2+col3). I need to aggregate the data after changing a value in one column which is part of the composite key. Col1 Col2 Col3 Total(sale) 1 2 3 0 1 4 3 2 1 6 3 5 1 3 3 10 Lets say I want to change the value of col2. If there is 4 I want to replace it with 2 If there is 6 I want to replace it with 2 After changing the val I want to aggregate the data and send it to another file. The Query I am using is: SELECT col1 ,col2= Max( CASE ChannelID WHEN '4' THEN '2' WHEN '6' THEN '2' ELSE [Col2] END) ,col3 ,SUM(ISNULL(Total,0)) FROM table ABC GROUP BY col1, Col2, Col3 Is the syntax right? The output I want to get is Col1 Col2 Col3 Total(sale) 1 2 3 7 1 3 3 10 I will appreciate your help.
Your problem is you are aggregating too soon, take a look at this: -- Setup DECLARE @TestTable AS TABLE ( col1 int, col2 int, col3 int, total int ) INSERT INTO @TestTable (col1, col2, col3, total) SELECT 1,2,3,0 UNION ALL SELECT 1,4,3,2 UNION ALL SELECT 1,6,3,5 UNION ALL SELECT 1,3,3,10 -- End Setup -- SQL 2005 and Above (using CTE) ; WITH CleanData AS ( SELECT col1, col2 = CASE col2 WHEN '4' THEN '2' WHEN '6' THEN '2' ELSE [Col2] END, col3,ISNULL(Total, 0) AS Total FROM @TestTable AS TT) SELECT col1, col2, col3, SUM(Total) FROM CleanData GROUP BY col1, col2, col3 -- SQL 2000 and above SELECT col1, col2, col3, SUM(Total) FROM (SELECT col1, col2 = CASE col2 WHEN '4' THEN '2' WHEN '6' THEN '2' ELSE [Col2] END, col3, ISNULL(Total, 0) AS Total FROM @TestTable AS TT) CleanData GROUP BY col1, col2, col3 You have to do the CASE value replacement, then in the "level" above it you do the aggregation on `Total`. I provided both solutions (2000 and 2005+) because you haven't mentioned what version of SQL Server you use.
