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

Question

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

Navigation

#1 by (2 votes)

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
FROM @tablaResultados r
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?

sql sql-server

asked by Jesús Miraanda 25.10.2018 в 19:29

source

1 answer

Special characters Codeigniter PHPExcel Only one expression can be specified in the select list when the subquery is not introduced with EXISTS

score 2 · Accepted Answer

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
        FROM @tablaResultados r
        GROUP BY DATEPART(DAY, r.entrega)
)
SELECT  T1.dia,
    T1.cilindros_a,
    T1.cilndros_v,
    T1.total_cilindros,
    SUM(T2.total_cilindros) AS 'acumulado'
    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 .