# How can I get a cumulative column that depends on conditions?

1

I need to find a way to get an accumulated for each of the records (my query currently returns the records per day), I have something like this:

``````SELECT
DATEPART(DAY, r.entrega) AS dia,
SUM(
CASE
WHEN (r.tipo = 1 AND r.idestado = 2) THEN 1 ELSE 0
END
) AS cilindros_a,
SUM(
CASE
WHEN (r.tipo = 1 AND r.idestado = 3) THEN 1 ELSE 0
END
) AS cilndros_v,
SUM(
CASE
WHEN (r.tipo = 1) THEN 1 ELSE 0
END
) AS total_cilindros
GROUP BY DATEPART(DAY, r.entrega)
``````

Which gives me results with the following structure:

``````dia cilindros_a cilndros_v  total_cilindros
1   1           0           1
2   3           3           6
3   1           0           1
4   0           0           0
5   1           0           1
6   2           0           2
7   2           0           2
``````

Get the accumulated of the week:

``````dia cilindros_a cilndros_v  total_cilindros acumulado
1       1           0           1           1
2       3           3           6           7
3       1           0           1           8
4       0           0           0           8
5       1           0           1           9
6       2           0           2           11
7       2           0           2           13
``````

I researched a bit and tried a couple of things like

``````SUM(total_cilindros) OVER(PARTITION BY GroupID ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acumulado
o
SUM(total_cilindros) OVER(ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acumulado
``````

But I have not been successful because I can not use the alias as a parameter of the summation.

I have also tried to nest the `SUM()` to avoid the alias, but in this case I returned an error in the syntax.

``````    SUM(
SUM(
CASE
WHEN (r.tipo = 1) THEN 1 ELSE 0
END
)
) OVER(ORDER BY RowId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acumulado
``````

In what way can I get this?

asked by Jesús Miraanda 25.10.2018 в 19:29
source

2

Maybe it is not the most optimal way, but it is simple to raise. You have a query already resolved, the idea is to work directly on it, using a `"COMMON TABLE EXPRESSION"` , this is valid from SQL 2008.

``````;WITH CTE AS (
SELECT  DATEPART(DAY, r.entrega)                                        AS dia,
SUM(CASE WHEN (r.tipo = 1 AND r.idestado = 2) THEN 1 ELSE 0 END)    AS cilindros_a,
SUM(CASE WHEN (r.tipo = 1 AND r.idestado = 3) THEN 1 ELSE 0 END)    AS cilndros_v,
SUM(CASE WHEN (r.tipo = 1) THEN 1 ELSE 0 END)                       AS total_cilindros,
ROW_NUMBER() OVER (ORDER BY DATEPART(DAY, r.entrega))               AS RN
GROUP BY DATEPART(DAY, r.entrega)
)
SELECT  T1.dia,
T1.cilindros_a,
T1.cilndros_v,
T1.total_cilindros,
FROM CTE T1
LEFT JOIN CTE T2
ON T2.RN <= T1.RN
GROUP BY
T1.dia,
T1.cilindros_a,
T1.cilndros_v,
T1.total_cilindros
``````

We take advantage of your exact query, and using a `LEFT JOIN` on itself `T2` , we build an accumulated simply adding the rows to the current `T1` .

answered by 25.10.2018 / 19:47
source