How to add current record to previous record by creating a cumulative?

Asked

Viewed 512 times

1

How to add current record to previous record,creating a cumulative?

SELECT 
        DAY(i.imp_data) AS DATA,
        m.mt_valor / m.mt_valor / CAST(DAY(LAST_DAY(NOW())) AS DECIMAL)* 100 AS META_DIARIA,
        SUM(i.imp_venda) / m.mt_valor * 100 AS VENDA
    FROM
        metas AS m
            LEFT JOIN
        importacao AS i ON m.mt_loja = i.imp_loja
    WHERE
        m.mt_loja = 1
    GROUP BY i.imp_data , i.imp_loja ;

The select above returns:

1   3.3333333333    4.289370
2   3.3333333333    4.469177
3   3.3333333333    6.114472
4   3.3333333333    2.988198
5   3.3333333333    2.903717
6   3.3333333333    4.254977
7   3.3333333333    2.825020
8   3.3333333333    4.320407
9   3.3333333333    3.985957
10  3.3333333333    5.961010
11  3.3333333333    2.829374
12  3.3333333333    2.855886
13  3.3333333333    2.859196
14  3.3333333333    2.812158
15  3.3333333333    4.580555
16  3.3333333333    3.297720
17  3.3333333333    4.736971
18  3.3333333333    2.325877
19  3.3333333333    2.488738
20  3.3333333333    2.059191

I want to add: record 1 + record 2

1   3.3333333333    4.289370
2   3.3333333333    4.469177

    66.666.666.666  8.758.547

And so on and so forth:

1   3.3333333333    4.289370
2   3.3333333333    4.469177
3   3.3333333333    6.114472
4   3.3333333333    2.988198
5   3.3333333333    2.903717
6   3.3333333333    4.254977
   199.999.999.998  25.019.911



    MT 1            MT2          ACUM 1         ACUM 2
1   33.333.333.333  6.114.472   33.333.333.333  6.114.472
2   33.333.333.333  2.988.198   66.666.666.666  9.102.670
3   33.333.333.333  2.903.717   99.999.999.999  12.006.387
4   33.333.333.333  4.254.977   133.333.333.332 16.261.364
php mysql

  • your cumulative will be the sum of all returned Lines ?

  • 1

    yes, I’ll edit the question to show how it could be

  • Do this in PHP, I believe you have a list of this object after you do the database search, just add one more line in the list with the sum of the fields, but it has a detail, its field has high values, this can exceed the size of some types of variables.

  • @ GOKU Ssjgod looks at the question,edited it, see what happened, could show me an example in php, because I’m actually using it on a google Dashboard.

  • Ah, I get it. you got two more columns ..

  • no, the columns I set as an example to you,

  • Let’s go continue this discussion in chat.

  • It has very common solutions, with @variables and with UNION ALL. Simpler would be to add in PHP. How would be good to give an optimized in your query. Would you like to post a minimal template on SQL Fiddle, with at least 10 data lines? It would facilitate as a starting point. Something else, what is the purpose of m.mt_valor / m.mt_valor in your query? It seems redundant to me because it results in 1, no? And there is one more question: if it is to use WHERE m.mt_loja = (numero de uma loja só), would simplify the LEFT JOIN importacao AS i ON m.mt_loja = i.imp_loja.

  • Hi @Bacco really made the sum and it worked thanks.

Show 4 more comments

1 answer

1


I don’t have a lot of time to think of something better right now, but it solves.

I hope someone gives a better answer.

  SELECT 
            DAY(i.imp_data) AS DATA,
            m.mt_valor / m.mt_valor / CAST(DAY(LAST_DAY(NOW())) AS DECIMAL)* 100 AS META_DIARIA,
            SUM(i.imp_venda) / m.mt_valor * 100 AS VENDA
        FROM
            metas AS m
                LEFT JOIN
            importacao AS i ON m.mt_loja = i.imp_loja
        WHERE
            m.mt_loja = 1
        GROUP BY i.imp_data , i.imp_loja
    Union all

    Select 0 , sum(d.META_DIARIA), sum(d.VENDA)
FROM 
    (SELECT 
            DAY(i.imp_data) AS DATA,
            m.mt_valor / m.mt_valor / CAST(DAY(LAST_DAY(NOW())) AS DECIMAL)* 100 AS META_DIARIA,
            SUM(i.imp_venda) / m.mt_valor * 100 AS VENDA
        FROM
            metas AS m
                LEFT JOIN
            importacao AS i ON m.mt_loja = i.imp_loja
        WHERE
            m.mt_loja = 1
        GROUP BY i.imp_data , i.imp_loja 
    )d;

You can mount a cursor as shown below.

declare @teste table
(
  DATA int,
  META_DIARIA float,
  VENDA float
)


declare @testeAux table -- crie uma tabela auxiliar
(
  DATA int,
  META_DIARIA float,
  VENDA float
)

insert into @teste values

(1, 3.3333333333,4.289370),
(2, 3.3333333333,4.469177),
(3, 3.3333333333,6.114472),
(4, 3.3333333333,2.988198),
(5, 3.3333333333,2.903717),
(6, 3.3333333333,4.254977),
(7, 3.3333333333,2.825020),
(8, 3.3333333333,4.320407),
(9, 3.3333333333,3.985957),
(10,3.3333333333,5.961010),
(11,3.3333333333,2.829374),
(12,3.3333333333,2.855886),
(13,3.3333333333,2.859196),
(14,3.3333333333,2.812158),
(15,3.3333333333,4.580555),
(16,3.3333333333,3.297720),
(17,3.3333333333,4.736971),
(18,3.3333333333,2.325877),
(19,3.3333333333,2.488738),
(20,3.3333333333,2.059191)

declare @DATA int,  @META_DIARIA float,  @VENDA float
DECLARE product_cursor CURSOR FOR   
select DATA,  META_DIARIA,  VENDA   from @teste -- altere para seu select existente


OPEN product_cursor  
FETCH NEXT FROM product_cursor INTO @DATA,  @META_DIARIA,  @VENDA  


WHILE @@FETCH_STATUS = 0  
BEGIN  
    insert into @testeAux (DATA,  META_DIARIA,  VENDA) 
    values (@DATA,  @META_DIARIA,  @VENDA );

    insert into @testeAux ( META_DIARIA,  VENDA) 
    select sum(META_DIARIA),  sum(VENDA)  from @testeAux where DATA is not null;

    FETCH NEXT FROM product_cursor INTO @DATA,  @META_DIARIA,  @VENDA    
    END  

CLOSE product_cursor  
DEALLOCATE product_cursor  

select * from @testeAux

This example was mounted on sql server, I believe it is not something difficult for you to mount it in mysql.

inserir a descrição da imagem aqui

  • As I said here, this may cause an error due to variable size.

  • 1

    I managed to hit your select, but in fact you did a total sum,I don’t need the total,I need them to accumulate 1 to 1

  • 1

    I’m studying your code to understand and apply to what I need,more for what I already see will come out the expected result,for now I did in php. Thanks again.

Browser other questions tagged php mysql

You are not signed in. Login or sign up in order to post.