compare and add in sequence with sql server

Question

through a table I intend to add the fields of col3 verifying if the value of col2 is equal to the following, doing a group by sequential.

col1 col2 col3
ind  seq1   5
ind  seq1   3
ind  seq1   7
ind  seq1   4
ind  seq2   5
ind  seq3   1
ind  seq3   3
ind  seq4   9
ind  seq5   6
ind  seq1   7

and I need the result to stay that way:

col1 col2 col3
ind  seq1   19
ind  seq2   5
ind  seq3   4
ind  seq4   9
ind  seq5   6
ind  seq1   7

Answer 1

FORM 1:

The following query:

-- Gera uma coluna "sequencia" para ser utilizada de referência
WITH referencia AS (
  SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS sequencia,
         t.*
    FROM tabela t
),
-- Calcula a coluna "agrupamento"
agrupamento AS (
  -- Pega o primeiro para definir como "agrupamento" = 1
  SELECT ref.*,
         1 AS agrupamento  
    FROM referencia ref
   WHERE ref.sequencia = 1
  UNION ALL
  -- Pega os subsequentes e apenas adiciona 1 ao agrupamento anterior se tiver mudado a "col2"
  SELECT atu.*,
         CASE atu.col2
           WHEN ant.col2 THEN ant.agrupamento
           ELSE ant.agrupamento + 1
         END AS agrupamento
    FROM agrupamento ant
         INNER JOIN referencia atu ON atu.sequencia = ant.sequencia + 1
)
-- Agrupa e soma os resultados
SELECT agr.col1,
       agr.col2,
       SUM(agr.col3) AS col3
  FROM agrupamento agr
 GROUP BY agr.agrupamento,
          agr.col1,
          agr.col2

• Utilizes WITH to create a table similar to the reference one but using ROW_NUMBER to generate the column sequencia;

• Calculate the grouping using the record with sequencia 1 to define the column agrupamento as 0 as anchorage;

• Takes subsequent records by adding 1 to the column agrupamento if you change your value on col2;

• Performs the SUM with the records generated and properly organized.

Observing: If there is an unaware column that can be used for sorting, just replace instead of (SELECT 0) in the ORDER BY of ROW_NUMBER.

---

FORM 2:

Use a cursor iterate the table and calculate the values by placing them in a variable table:

DECLARE @resultado TABLE(sequencia INT IDENTITY,
                         col1      VARCHAR(10),
                         col2      VARCHAR(10),
                         col3      INT);

DECLARE @col1             VARCHAR(10),
        @col2             VARCHAR(10),
        @col3             INT,
        @ultima_sequencia INT,
        @ultimo_col1      VARCHAR(10),
        @ultimo_col2      VARCHAR(10);

-- Percorre os registros da tarefa
DECLARE cursor_agrupamento CURSOR LOCAL FAST_FORWARD FOR
  SELECT t.col1,
         t.col2,
         t.col3
    FROM tabela t;
OPEN cursor_agrupamento
FETCH NEXT FROM cursor_agrupamento INTO @col1,
                                        @col2,
                                        @col3
WHILE @@FETCH_STATUS = 0
BEGIN
  -- Caso não seja o mesmo "col1" ou não seja o mesmo "col2" do registro anterior insere um novo registro
  IF @col1 <> ISNULL(@ultimo_col1, 0) OR @col2 <> ISNULL(@ultimo_col2, 0)
  BEGIN
    INSERT INTO @resultado(col1, col2, col3)
                    VALUES(@col1, @col2, @col3);

    -- Guarda o último "IDENTITY" inserido
    SET @ultima_sequencia = SCOPE_IDENTITY();
  END
  ELSE
  BEGIN
    -- Atualiza o valor somando o valor do atual
    UPDATE r
       SET r.col3 = r.col3 + @col3
      FROM @resultado r
     WHERE r.sequencia = @ultima_sequencia;
  END;

  -- Atualiza os valores que serão verificados na próxima interação
  SET @ultimo_col1 = @col1;
  SET @ultimo_col2 = @col2;

  FETCH NEXT FROM cursor_agrupamento INTO @col1,
                                          @col2,
                                          @col3
END;
CLOSE cursor_agrupamento;
DEALLOCATE cursor_agrupamento;

SELECT r.col1,
       r.col2,
       r.col3
  FROM @resultado r
 ORDER BY r.sequencia;

---

WITH

A CTE (common table expression) provides the significant advantage of being able to self-reference, thus creating a recursive CTE. A recursive CTE is one in which an initial CTE is run repeatedly to return subsets of data until the complete result is obtained.

---

GROUP BY

Groups a set of selected rows into a set of summary rows by the values of one or more columns or expressions in the SQL Server 2014. A row is returned to each group. The aggregation functions in the list of <seleção> of the clause SELECT provide information about each group instead of individual lines.

---

ROW_NUMBER

Returns the sequential number of a row in a partition of a result set, starting at 1 for the first row of each partition.

---

See working on SQL Fiddle.