Sum datagrid column values by criteria
-
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.
-
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.
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*.
-
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*.
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
-
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
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.
-
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.
-
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 ap
-
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
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.
-
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.
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 :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:
-
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 :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:
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.
-
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 :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:
Incidentally (again, if you don't understand so yet), my recommendation to go ahead and iterate the cells was sarcastic. You probably didn't even find documentation for that, did you? (I haven't looked, but I know any such controls I've worked with -- in many languages -- either have no such documentation, or the process is so obscure it would be an extremely advanced skill based for instance from careful, meticulous study of code such as the VCL which comes with Delphi/C++ Builder.) My idea was that you are vacillating. You first wanted SQL. Then you did not. Everyone has to study their options carefully. It is by studying them that you should have opted not to want to derive your data any longer by iterating your control. That idea is plain silly. The data already exists in a form by which it can readily be iterated or processed by SQL. *That* is why documentation for iterating the control [probably] does not exist [in any plain, accessible form]. When it comes down to it, this just a plain/usual problem where a first issue is to identify what to process. You want to process the data. It exists (even in the informal table/AS/400 file) in a form which is more conducive to processing. Read the AS/400 file to populate a formal SQL table with the same tools you have to use for the other table, and everything is compatible, and easily processed. Simple.