I am making an app and I want to add the values of the SUBTOTAL column classifying it by brand ( BRAND column ), the problem is that the brands will vary. In the first instance it only occupied two marks and solved the problem with this code...
Sub Sumar()
Dim totalA As Double = 0
Dim totalB As Double = 0
For Each fila As DataGridViewRow In dgvDatos.Rows
If fila.Cells("SUBTOTAL").Value Is Nothing Then
Exit Sub
ElseIf fila.Cells("MARCA").Value = "A" Then
totalA += Convert.ToDouble(fila.Cells("SUBTOTAL").Value)
ElseIf fila.Cells("MARCA").Value = "B" Then
totalB += Convert.ToDouble(fila.Cells("SUBTOTAL").Value)
End If
Next
lblA.Text = Format(totalA, "$ #,##0.000")
lblB.Text = Format(totalB, "$ #,##0.000")
End Sub
But now the marks can be variables and the way I'm handling it I would have to declare N variables to store the result per mark. Any suggestions to optimize my operation?
Thanks in advance!!
You can use the algorithm known as control cutting . In this link that I left you they define it in a simple way:
"The control cut is a process in which starting from records ordered by the value of one or more fields, (called key fields or key or ordering criteria) they are processed in categories determined by the ordering criteria. In other words , an ordered set of records is processed into subsets determined by the order criteria."
In your case, you should first order the dgvDatos table by BRAND, in order to use this attribute as a classifier. Here is how you could start modifying your code (I don't know much about vb, so don't take this example as a solution):
In this way, by ordering your table as a first step, you would have all the rows belonging to MARK 'A', then all the rows of the following MARK and so on. The algorithm adds all the SUBTOTALS belonging to the first brand, until it finds a different one, and there it sets the brand variable with the new brand (the one after the first defined, which is 'A') to start the whole process again.
It uses a dictionary, it does not require having the data sorted.
In the code where you do the sum:
I do it from memory, I don't have visual studio at hand to test
If you have your Datagridview associated with a DataTable (dgvDatos.DataSource = TablaDgvDatos) you can use the Compute method of the DataTable.
If these marks are going to be variable over time and you mean not having to modify your code when there is a new mark, for example, specify how you read the marks you want to add in order to suggest a solution.
If the data set is in the database, you have the following command
SELECT
that would be giving you what you are looking for:The best way to display this result is another grid, but it has the advantage that no matter how many Marks you have it will work.
You can create a stored procedure or you can launch the execution of the query against the database.