How to RowCount DataGridView doing something like where clause

Question

0.00/5 (No votes)

See more:

Month	Quarter	Product	Option	Amount	Value
10.2008	4.Q	Plate	PK	400	BN
10.2008	4.Q	Plate	PK	4000	BN
10.2008	4.Q	Shoe	BK	5000	BN
04.2008	2.Q	Plate	BK	2000	BP
03.2008	1.Q	Rice	BK	800	BN
06.2008	2.Q	Rice	PK	300	BN
03.2008	1.Q	Fish	PK	500	BN
03.2008	1.Q	Fish	BK	900	BP
07.2008	3.Q	Rice	PK	7000	DN
07.2008	3.Q	Shoe	PK	3000	BN
07.2008	3.Q	Shoe	PK	8000	BN
12.2008	4.Q	Shoe	BK	4500	BP
05.2008	2.Q	Tofu	BK	1200	BN
01.2008	1.Q	Tofu	BK	8500	BP
09.2008	3.Q	Shoe	PK	6500	BP
05.2008	2.Q	Rice	PK	720	DN

So i wanna show / display some informations  based on my Datagridview records. I have three or more Lables or a list view (don’t exactly know which one is the best) and a Button (btnShowInfo).
What I wanna archieve is when I click the Button, following records should be displayed either in the lables or list view:
1.	The number of 4.Q’s, 3.Q’s, 2.Q and 1.Q. (Example: Nr.: Q4 =4, 3.Q = 4  etc.)
2.	The sum of Amount values where Quarter is Q4, Q3, Q2 and Q1. (something like this: SUM(Amount) WHERE Quarter = Q4, Q3, Q2 and Q1)
3.	The sum  of Amount values where Quarter is Q4, Q3, Q2, Q1 AND Option = Pk AND value = BN

Is it possible to archieve such a goal OR any Advice? I can count and sum the datagridview rows but don’t know how accomplish my above question.

Possible Results shd look like this:
1.Nr. of quartal period
1.Q = 4
2.Q = 4
3.Q = 4
4.Q = 4

2. Total Amount for:
1.Q = 10.700
2.Q = 4220
3.Q = 24.500
4.Q = 13.900

3.Total Amount where Quartar = 1.Q && Option = PK && Value = BN
Total Amount where Quartar = 1.Q && Option = PK && Value = BP
Total Amount where Quartar = 1.Q && Option = PK && Value = DN
1300      PK & BN
null        PK & BN
null   PK & DN

4.Total Amount where Quartar = 2.Q && Option = PK && Value = BN
Total Amount where Quartar =2.Q && Option = PK && Value = BP
Total Amount where Quartar = 2.Q && Option = PK && Value = DN
300       PK & BN
2000     PK & BN
720       PK & BN

Posted 3-Dec-14 1:59am

mikybrain1

Updated 3-Dec-14 2:01am

v2

Add a Solution

2 solutions

Solution 1

Create a stored procedure and get all your tables at once as a dataset.

The query to sum it would be

SELECT Quarter, SUM(Amount), Count(*) FROM your-table WHERE your-conditions
GROUP BY Quarter.

Then mix and match the conditions :)

I'd go with grid, list or other aggregation rather then labels.

If this helps please take time to accept the solution. Thank you

Posted 3-Dec-14 2:25am

Sinisa Hajnal

Comments

mikybrain1 3-Dec-14 8:38am

Hi Sinisa
I do have ALL the statements already in a sp. but how should the implementations code in the form be like.
I can sum the columns something like this

// Sum and display Amount total
decimal sum = 0;
for (int i = 0; i < dgv1.Rows.Count; ++i)
{
decimal dec = 0;
decimal.TryParse(this.dgv1[8, i].FormattedValue.ToString(), out dec);
sum += dec;
}

sum = Math.Round(sum);
lbSum.Text = " Amount-Total " + sum.ToString();

So how can i solve it like this way?And how can i used multiple sp within a Button click method?

Sinisa Hajnal 3-Dec-14 9:08am

Why? Databases are good with summing your values up even if you go to million rows. You have simple get that gets you data, you should have another query to get summary. The way you're doing it is prone to errors, heavy on testing and sloooow comparing to the database solution.

Sinisa Hajnal 3-Dec-14 9:10am

Anyhow, if you really want to do it this way, check datatable.RowFilter, datatable.Compute and DataView.ToTable method. In RowFilter you set your condition, create datatable variable and call your-table.DefaultView.ToTable to get the table with only those rows you need. Then do with them counts, computes etc...no need for loops...

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-12-03T11:52:00

You can use Linq. For example:
1) to get count of quarters:

C#

var qry = from DataGridViewRow dgvr in dataGridView1.Rows
          group dgvr.Cells["Quarter"].Value  by dgvr.Cells["Quarter"].Value  into grp
          select new { Quarter = grp.Key, CountOf = grp.Count() };
           StringBuilder sb = new StringBuilder();
           foreach (var ele in qry)
           {
               sb.AppendLine (String.Format("{0} = {1}", ele.Quarter, ele.CountOf));
           }
           MessageBox.Show(sb.ToString(),"Message...");

Result:

4.Q = 4
2.Q = 4
1.Q = 4
3.Q = 4

2) to get total amount:

C#

qry = from DataGridViewRow dgvr in dataGridView1.Rows
          group dgvr  by dgvr.Cells["Quarter"].Value  into grp
          select new
               {
                   Quarter = grp.Key,
                   Amount = grp.Sum(x=>Convert.ToInt32( x.Cells["Amount"].Value))
               };
sb = new StringBuilder();
foreach (var ele in qry)
{
    sb.AppendLine(String.Format("{0} = {1}", ele.Quarter, ele.Amount));
}
MessageBox.Show(sb.ToString(), "Message...");

Result:

4.Q = 13900
2.Q = 4220
1.Q = 10700
3.Q = 24500

To be able to write query 3. and 4., change group by this way:

C#

group dgvr by new
        {
            a = dgvr.Cells["Quarter"].Value,
            b = dgvr.Cells["Option"].Value,
            c = dgvr.Cells["Value"].Value
        } into grp

Try!
Good luck!