CODEWITHSUNDEEP
c#mysql
Technoo
Technoo

Reputation: 13

Summing Mysql values on C#

I have a mysql table with some user data, it has some products that holds the quantity, I wanted to sum each product quantity to get the product total quantity, I can't do Select 'my product name here' because I need to get all the products automatically, they can change at any moment so i'm creating a temporary table and deleting the clients info columns to get only the products from it. The mysql command i'm using to get only the products from my table:

CREATE TEMPORARY TABLE TemporaryTable 
    SELECT RoteiroDia.* 
    FROM Clientes 
        INNER JOIN RoteiroDia ON Clientes.id=RoteiroDia.idCliente 
        INNER JOIN NomeRotas ON Clientes.idRota=NomeRotas.id 
        INNER JOIN Vendedores ON RoteiroDia.idVendedor=Vendedores.id 
        INNER JOIN Veiculos ON RoteiroDia.idPlaca=Veiculos.id 
    WHERE NRotas = 'placeHere' 
    AND Vendedores.Nome = 'nameHere' 
    AND Veiculos.Placa = 'nameHere' 
    AND RoteiroDia.Data = 'dateHere'; 

    ALTER TABLE TemporaryTable 
        DROP COLUMN FormaPagamento, 
        DROP COLUMN Obs, 
        DROP COLUMN id, 
        DROP COLUMN Data, 
        DROP COLUMN idRota, 
        DROP COLUMN idCliente, 
        DROP COLUMN pos, 
        DROP COLUMN idVendedor, 
        DROP COLUMN idPlaca, 
        DROP COLUMN Debito, 
        DROP COLUMN Acerto, 
        DROP COLUMN Diferença, 
        DROP COLUMN DinheiroRecebido, 
        DROP COLUMN AVista, 
        DROP COLUMN Despesas, 
        DROP COLUMN NovaVendas, 
        DROP COLUMN Entradas; 

    SELECT * FROM TemporaryTable;

What the mysql code gives me: image

To sum them all into each "Product", I'm using this c# code here: I'm using the rdr2.fieldcount to get the product quantity (9 currently), then i'm looping through it and assigning the rdr.getint(0) to the produtoQuantidadeList[i]

produtoQuantidadeList = new List<int>(new int[rdr2.FieldCount]);
int i = 0;
while (rdr2.Read())
{
    produtoQuantidadeList[i] += rdr2.GetInt32(0);
    if (i < rdr2.FieldCount - 1)
        i++;
    else
        i = 0;
}

I'm then using this here to debug the quantity:

for(int i = 0; i < produtoQuantidadeList.Count; i++)
    Console.WriteLine($"***** {produtoQuantidadeList[i]}");

But my c# code isnt summing them all correctly, apparently it cant get all the values from the table

What my table SUM() with the third product does: Image

Summing with my code: The third product (number 2)

Upvotes: 0

Views: 148

Answers (2)

Gellio Gao
Gellio Gao

Reputation: 843

The problem should be fixed like below:

produtoQuantidadeList = new int[rdr2.FieldCount];
while (rdr2.Read())
{
    // Loop all fields for each record
    for(int i=0; i < rdr2.FieldCount; i++)
    {
        // Sum the value of each field
        produtoQuantidadeList[i] += rdr2.GetInt32(i);
    }
}

Upvotes: 1

Caius Jard
Caius Jard

Reputation: 74605

It's a bit over complicated.. Did you know you can remove columns from a result set by simply not mentioning them?.. If your Person table has Name and Age columns and you want a list of all the Names, you don't need to create a temp table from Person then drop the Age column.. you just don't select it:

SELECT Name FROM Person --no age column mentioned

Now to sum all of column 9 grouped by column 0:

SELECT Columns,You,Want,From,Roteiro, 
  SUM(column9NameHere) OVER(PARTITION BY galões) total_by_galoes
FROM
  ...

Of course, your screenshot only goes up to column 8 so I have no idea what your column 9 is called. Substitute the name in

Upvotes: 0

Related Questions