Re: Sum datagrid column values by criteria - C# Discussion Boards

16-Mar-07 4:18

Hi everyone. I have Windows.Forms.DataGrid with two columns. The first one has string values which may repeat in the column. The second one has integers. My question is how can I sum the integers where I have string values which appear in more than one row in the first column?
I need something like the "group by" clause in a sql select statement.

mike montagne16-Mar-07 7:16

16-Mar-07 7:16

This is certainly not enough information to write your SQL statement for you. I suggest you Google SQL. There are dozens of introductory SQL pages online -- and many of the tutorials should get you where you need to go in an hour or so of study. You'll also understand SQL much better. From your question, it sounds like you *might* need an inner join -- *might*.

choopie17-Mar-07 9:42

17-Mar-07 9:42

Hello Mike, I'm talking about this:
DataGrid columns

Column1 Column2
-----------------------
Month Amount
02.2007 60
02.2007 30
03.2007 50
04.2007 60

I want to add rows in the grid which sum the Amount values by Month like this:
-----------------------
02.2007 90
03.2007 50
04.2007 60
-----------------------
I need C# class not a SQL statement

mike montagne17-Mar-07 10:25

17-Mar-07 10:25

Iterate the cells, read the values however you have to, and build your sums on whatever conditions you want to comply with. Whatever control you are using should document exactly how to do so.

choopie17-Mar-07 10:46

17-Mar-07 10:46

Thanks for the help Mike.

mike montagne18-Mar-07 7:59

18-Mar-07 7:59

You're welcome.

Incidentally, you should understand this as a basic model for what you are trying to do:

SQL is an excellent way to eliminate the conditional logic you are going to have to perform. In other words, if your source data is organized in tables (and rarely if ever is there a good reason for such data not to be -- maybe unless it's just a few values), then you can eliminate sometimes huge bodies of code and costly processes by first applying SQL to your source data. If your SQL statement can apply all the necessary conditions to reduce/filter its result set to *just* the records/rows you should be processing, then you can even get the sum in one fell swoop.

That is the ideal approach, and you should always tackle such a problem this way (even if you didn't know SQL before).

Many years ago, I discovered this in the process of re-writing a huge client/server application. The application I had to replace had gone down. No source existed. It processed a huge number of records (programmatically), finally producing billing information related to numerous categories of property acreage, altogether assembled under each owner. The first so many acres were taxed at such and such a rate, the next so many acres (per owner, not per property) were charged at further scaled rates, etc.

The original application took 3 1/2 hours to process their tables. The process was complex, and you could understand why it might take so long, particularly as substantial engineering challenges existed to any degree of refinement.

I spent the time to develop those substantial engineering innovations, and my first stab at the process processed their data in only 11 seconds. My clients practically wept they were so ecstatic.

The innovations which made the process so fast involved developing a system which never had to look at the same data twice, and elimination of all conditional processing possible -- conditional logic was reduced to the absolute minimal process.

Nonetheless, SQL tools were relatively new then (in the first days of OOP), and I had an idea that I could re-write my process -- actually eliminate most of it -- by devising an SQL statement. A few days later I demonstrated this process. Most of my time was in developing a universal interface by which they could prescribe any conceivable tax scheme in seconds. The SQL process produced billing information -- ready to print in reports -- in only 3 seconds. Moreover, the results of the prescription could be evaluated *before* application, and could be applied in 1 second to "permanent" records. The application *still* however could be reversed (unapplied) in 1 second. True story. Eyes got wet. Heads just shook. Now, for the first time ever, they could have a board meeting and decide the tax rates necessary to cover costs in seconds.

So your process takes these things into account. What is the fastest and most convenient way to do what you have to do with the resources or tools available to you?

SQL is powerful and fast. No way can you iterate your cells and produce the answer as quickly. Way back I read a benchmark that has always stuck in my head. With this certain SQL DBMS, a Pentium 90 could process a million record table and produce a [I forget just how many] many-record result set in under a second.

Think about SQL. You will use it often if you operate on data and write efficient code.

mike montagne18-Mar-07 8:17

18-Mar-07 8:17

I had to take a look at this again -- just curious.

I don't know how you end up with a "Month" structure like this... but it looks like bad practice. Express your values as Y.M[.D], instead of M.Y. Your records will not be in chronological order -- they will be grouped by month (which is not likely to be conducive to any process, and may impose redundant overhead to practical processes).

You are citing values in each column, and the only thing I can *notice* is the lacking value in the result column is the least value, 30. You neglect to mention what your forking criteria is -- greater than or equal to 30 for instance, or greater than or equal to 50 will produce the same result set.

Now, an SQL statement can do all this in a single expression. So, do you want to spend an hour on SQL (to know that hour forever to write one SQL statement now), or do you want to write all the code to iterate cells and read and sum values?

Those are your questions.

Resist any temptation to think the latter is easier because it eliminates needing to learn SQL. For example, I don't think I've ever iterated the cells of a *data* grid (of all things) in my life -- and I would certainly question the professional credentials of anyone who ever preferred that approach over SQL.

That means a lot of other people have never iterated the cells of a *data* grid, either.

choopie18-Mar-07 22:25

18-Mar-07 22:25

Well, the point is that I couldn't join two tables from different data sources. One table from SQL Server database and another one which is represented as a file in AS/400 machine. I'm sure this will make you wonder D'Oh! | :doh:

why I need such data to join from these sources. This is not designed by me I only have to make this join and sum the Amount values by Month. But I couldn't. I only succeeded to make a connection to each of the sources and to select the data without the join (just for test). The file in AS/400 contains the Amount values and some other columns. The months i take from SQL Server table. I prefer using SQL in separate logic layer too but in this case the join doesn't work. Maybe I'm wrong to not to keep trying it with the SQL. I thought it might be easier to do it in C# class. Confused | :confused:

mike montagne19-Mar-07 6:25

19-Mar-07 6:25

OK. In this case you have a system design which obstructs proper processing. That's often a political matter. Sometimes it's not a matter where proper design wins out -- the "IT" team may jealously guard the data and its processing... whatever.

In this case, extract a bona fide table from the data and process it how you ought to.

Don't let people who don't know what they're doing stand in the way of getting the job done right. Stomp toes. Lecture. Whatever. Embarrass them with how they are obstructing proper processing. No good technical person stands in the way of doing a job properly. If they worked for me, they'd already be outed.

mike montagne19-Mar-07 6:36