Count total of distinguishable listview items

Question

3.11/5 (3 votes)

See more:

I have a listview (Listview 1) which I filled with data. Now I want to charge a second listview (listview 2) on the first listview. This should indicate Collem [1] listview1 and count the number of occurrences of the name. See Example listview 2.

Listview 1

ID *  NAAM  *  DATE
1 .     MO            01-01-2016
1 .     DE             01-01-2016
1 .     MO            02-01-2016
1 .     MO            02-01-2016
1 .     DE             02-01-2016
1 .     MO            03-01-2016
1 .     MO            02-01-2016

Listview 2

NAAM  *  Total count
MO    (TOTAL 5)         
DE     (TOTAL 3)

What I have tried:

I found this; but this script makes no distinction in names..

C#

var query = listView.Items
                    .Cast<listviewitem>()
                    .Where(item => item.SubItems[3].Text == "asdf");
foreach (var item in query)
{
    ...
}

Posted 9-Aug-16 1:25am

MaikelO1

Updated 9-Aug-16 9:16am

Maciej Los

v2

Add a Solution

Comments

Karthik_Mahalingam 9-Aug-16 9:07am

how you are binding the data ?
post the binding code.

MaikelO1 9-Aug-16 9:11am

private void Registratiefilter()
{
ListBoxUren.TabIndex = 0;
ListBoxUren.Items.Clear();
ListBoxUren.View = View.Details;

ListBoxUren.MultiSelect = false;
ListBoxUren.FullRowSelect = true;

using (var connection = new SqlConnection(connectionstring))
{

var query = "SELECT ID, Start, Stop, Pauze, SoortOmschrijving, Naam, IDProjecten, Organisatie, AardOmschrijving, ExtraOmschrijving, (Projectnummer + ' - ' + Organisatie + ' - ' + Referentie) AS REFProject, (MaterieelNR + ' - ' + Materieelstuk) AS REFMaterieel FROM UrenREG LEFT OUTER JOIN Medewerkers ON UrenReg.FK_IDMedewerker = Medewerkers.IDMedewerker LEFT OUTER JOIN UrenRegSoortActiviteit ON UrenReg.FK_SoortActiviteitID = UrenRegSoortActiviteit.SoortID LEFT OUTER JOIN Projecten ON UrenReg.FK_ProjectID = Projecten.IDProjecten LEFT OUTER JOIN Klanten ON Projecten.FK_IDKlant = Klanten.IDKlanten LEFT OUTER JOIN UrenRegAardVanActiviteit ON UrenReg.Fk_AardVanActiviteitID = UrenRegAardVanActiviteit.AardVanActiviteitID LEFT OUTER JOIN Materieel ON UrenReg.FK_materiaalID = Materieel.IDMaterieel LEFT OUTER JOIN UrenRegExtra ON UrenReg.FK_ExtraID = UrenRegExtra.ExtraID WHERE Start like @Date";

using (var adapter = new SqlDataAdapter(query, connection))
{

SqlParameter parm1 = adapter.SelectCommand.Parameters.AddWithValue("@Date", "%" + LB_DatePick.Text + "%");

try
{
DataTable ProjectLijst = new DataTable();
adapter.Fill(ProjectLijst);

for (int i = 0; i < ProjectLijst.Rows.Count; i++)
{
DataRow dr = ProjectLijst.Rows[i];
ListViewItem listitem = new ListViewItem(dr["Naam"].ToString());
listitem.SubItems.Add(dr["Start"].ToString());
listitem.SubItems.Add(dr["Stop"].ToString());
listitem.SubItems.Add(dr["Pauze"].ToString());
listitem.SubItems.Add(dr["AardOmschrijving"].ToString());
listitem.SubItems.Add(dr["SoortOmschrijving"].ToString());
listitem.SubItems.Add(dr["REFProject"].ToString() + dr["REFMaterieel"].ToString());
listitem.SubItems.Add(dr["ExtraOmschrijving"].ToString());

listitem.Tag = dr["ID"];
ListBoxUren.Items.Add(listitem);

ListBoxUren.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent);
ListBoxUren.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
}
}
catch (Exception)
{

Karthik_Mahalingam 9-Aug-16 9:14am

you need distinct ,count of NAAM column in the second listbox?

MaikelO1 9-Aug-16 9:18am

Yes

Patrice T 9-Aug-16 11:10am

Use Improve question to update your question.
So that everyone can pay attention to this information.

3 solutions

Solution 2

refer this example

C#

private void Form3_Load(object sender, EventArgs e)
       {
           listView1.View = View.Details;
           listView1.GridLines = true;


           ListViewItem item1 = new ListViewItem("1");
           item1.SubItems.Add("MO");
           item1.SubItems.Add("01-01-2016");
           ListViewItem item2 = new ListViewItem("1");
           item2.SubItems.Add("DE");
           item2.SubItems.Add("01-01-2016");
           ListViewItem item3 = new ListViewItem("1");
           item3.SubItems.Add("MO");
           item3.SubItems.Add("01-01-2016");
           ListViewItem item4 = new ListViewItem("1");
           item4.SubItems.Add("MO");
           item4.SubItems.Add("01-01-2016");
           ListViewItem item5 = new ListViewItem("1");
           item5.SubItems.Add("DE");
           item5.SubItems.Add("01-01-2016");
           ListViewItem item6 = new ListViewItem("1");
           item6.SubItems.Add("MO");
           item6.SubItems.Add("01-01-2016");
           ListViewItem item7 = new ListViewItem("1");
           item7.SubItems.Add("MO");
           item7.SubItems.Add("01-01-2016");


           listView1.Columns.Add("ID"  );
           listView1.Columns.Add("NAAM");
           listView1.Columns.Add("DATE" );

           listView1.Items.AddRange(new ListViewItem[] { item1, item2, item3, item4,item5,item6,item7 });

           int columnToCheckIndex = 1; // set the index value of the column to be checked
          List<string> lst = new List<string> ();
           foreach (ListViewItem item in listView1.Items)
               lst.Add(item.SubItems[columnToCheckIndex].Text);
           var data = lst.GroupBy(k => k).Select(k => new { key = k.Key, count = k.Count() }).ToList();
           foreach (var item in data)
           {
               ListViewItem lvi = new ListViewItem(item.key);
               lvi.SubItems.Add(item.count.ToString());
               listView2.Items.Add(lvi);

           }

           listView2.Columns.Add("NAAM");
           listView2.Columns.Add("Total Count");
           listView2.View = View.Details;
           listView2.GridLines = true;





       }

Posted 9-Aug-16 4:33am

Karthik_Mahalingam

Comments

[no name] 9-Aug-16 11:34am

A 5, especally for introducing int columnToCheckIndex = 1; to make maintenance more easy.

Karthik_Mahalingam 9-Aug-16 11:36am

Thank you 0x01AA

Maciej Los 9-Aug-16 15:17pm

Karthik, sorry, but you're wrong. Please, read my answer to find out why...

Karthik_Mahalingam 10-Aug-16 0:29am

Hi Maciej
The difference i found was, i am picking the data from an existing listbox, whereas you are getting it from a datatable source.
for this reason only i have asked the OP at the first place " how you are binding the data ?
post the binding code.
but he has posted some code dump which is not much relevant to the question he asked, so i just demoed him some piece of code..
The approach might be wrong, but it works correct maciej :)
However your solution has detailed information in which OP needs or anyone in future.

Thanks
karthik

Maciej Los 10-Aug-16 1:46am

Well... I have say Sorry, because i wasn't clear enough. When i said, that you're wrong, i thought about "data context", i didn't say that your solution is wrong. It's correct, but we have keep in mind that we should work on data, not on controls. That's the reason i voted arbitrarily (3).
Cheers,
Maciej

Karthik_Mahalingam 10-Aug-16 2:08am

cool :)

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 · 2016-08-09T09:16:00

MaikelO1[^] wrote in comment:

C#

private void Registratiefilter()
        {
            ListBoxUren.TabIndex = 0;
            ListBoxUren.Items.Clear();
            ListBoxUren.View = View.Details;

            ListBoxUren.MultiSelect = false;
            ListBoxUren.FullRowSelect = true;

            using (var connection = new SqlConnection(connectionstring))
            {

                var query = "SELECT ID, Start, Stop, Pauze, SoortOmschrijving, Naam, IDProjecten, Organisatie, AardOmschrijving, ExtraOmschrijving, (Projectnummer + ' - ' + Organisatie +  ' - ' + Referentie) AS REFProject, (MaterieelNR + ' - ' + Materieelstuk) AS REFMaterieel  FROM UrenREG LEFT OUTER JOIN Medewerkers ON UrenReg.FK_IDMedewerker = Medewerkers.IDMedewerker LEFT OUTER JOIN UrenRegSoortActiviteit ON UrenReg.FK_SoortActiviteitID = UrenRegSoortActiviteit.SoortID LEFT OUTER JOIN Projecten ON UrenReg.FK_ProjectID = Projecten.IDProjecten LEFT OUTER JOIN Klanten ON Projecten.FK_IDKlant = Klanten.IDKlanten LEFT OUTER JOIN UrenRegAardVanActiviteit ON UrenReg.Fk_AardVanActiviteitID = UrenRegAardVanActiviteit.AardVanActiviteitID LEFT OUTER JOIN Materieel ON UrenReg.FK_materiaalID = Materieel.IDMaterieel LEFT OUTER JOIN UrenRegExtra ON UrenReg.FK_ExtraID = UrenRegExtra.ExtraID WHERE Start like @Date";
                
                using (var adapter = new SqlDataAdapter(query, connection))
                {

                    SqlParameter parm1 = adapter.SelectCommand.Parameters.AddWithValue("@Date", "%" + LB_DatePick.Text + "%");

                    try
                    {
                        DataTable ProjectLijst = new DataTable();
                        adapter.Fill(ProjectLijst);

                        for (int i = 0; i < ProjectLijst.Rows.Count; i++)
                        {
                            DataRow dr = ProjectLijst.Rows[i];
                            ListViewItem listitem = new ListViewItem(dr["Naam"].ToString());
                            listitem.SubItems.Add(dr["Start"].ToString());
                            listitem.SubItems.Add(dr["Stop"].ToString());
                            listitem.SubItems.Add(dr["Pauze"].ToString());
                            listitem.SubItems.Add(dr["AardOmschrijving"].ToString());
                            listitem.SubItems.Add(dr["SoortOmschrijving"].ToString());
                            listitem.SubItems.Add(dr["REFProject"].ToString() + dr["REFMaterieel"].ToString());
                            listitem.SubItems.Add(dr["ExtraOmschrijving"].ToString());

                            listitem.Tag = dr["ID"];
                            ListBoxUren.Items.Add(listitem);

                            ListBoxUren.AutoResizeColumns(ColumnHeaderAutoResizeStyle.ColumnContent);
                            ListBoxUren.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize);
                        }
                    }
                    catch (Exception)
                    {

First of all, second of all and finally: You should work on data, not on controls!

As to your confusion about how to get distinct count of some data:
You've got 2 ways to achieve that:
1) you can "ask" your database and return result to the second ListView
2) you can get the count of distinct values by grouping data from the datatable via using Linq

Ad 1)

SQL

SELECT Name, COUNT(DISTINCT Name)
FROM YourTable
GROUP BY Name

Ad 2)

C#

var result = ProjectLijs.AsEnumerable()
    .GroupBy(x => x.Field<string>("Name"))
    .Select(grp => new
          {
              Name = grp.Key,
              Count = grp.Distinct().Count()
          })
     .ToList()

All what you have to do right now, is to bind that data to the second ListView.

Try the method you prefer! Note, that Linq solution for large data amount may cause efficiency issues.

For further details, please see:
LINQ to DataSet[^]
LINQ to DataSet Examples[^]
Queries in LINQ to DataSet[^]
Querying DataSets (LINQ to DataSet)[^]
101 LINQ Samples in C#[^]

[EDIT]
Example:

C#

DataTable dt = new DataTable();

dt.Columns.Add(new DataColumn("ID", typeof(int)));
dt.Columns.Add(new DataColumn("Name", typeof(string)));
dt.Columns.Add(new DataColumn("Date", typeof(DateTime)));

dt.Rows.Add(new object[]{1, "MO", new DateTime(2016,1,1)});
dt.Rows.Add(new object[]{1, "DE", new DateTime(2016,1,1)});
dt.Rows.Add(new object[]{1, "MO", new DateTime(2016,1,2)});
dt.Rows.Add(new object[]{1, "MO", new DateTime(2016,1,2)});
dt.Rows.Add(new object[]{1, "DE", new DateTime(2016,1,2)});
dt.Rows.Add(new object[]{1, "MO", new DateTime(2016,1,3)});
dt.Rows.Add(new object[]{1, "MO", new DateTime(2016,1,2)});

var result = dt.AsEnumerable()
		.GroupBy(x=>x.Field<string>("Name"))
		.Select(grp=>new
			{
				Name = grp.Key,
				Count = grp.Distinct().Count()
			});

Above code returns:

MO 5 
DE 2

[/EDIT]

User 11060979 · Accepted Answer · 2016-08-09T04:05:00

Please take this solution not too serious, I have no clue about this Linq stuff. I have also no idea whether this is the correct way. But if I have a look to the result, for me at least it solves the request.

C#

private void buttonListViewLinq_Click(object sender, EventArgs e)
{
    // Test Source ListView
    listViewLinq.Items.Clear();
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "A", "2016-08-01"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "A", "2016-08-02"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "B", "2016-08-03"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "B", "2016-08-04"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "B", "2016-08-05"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"2", "B", "2016-08-06"}));
    listViewLinq.Items.Add(new ListViewItem (new string[]{"1", "C", "2016-08-07"}));

    // Get IEnumerable to go on with linq
    IEnumerable<ListViewItem> lv = listViewLinq.Items.Cast<ListViewItem>();

    // Here grouping happens
    var res = lv.GroupBy(gb => gb.SubItems[1].Text)
                .Select(group => new { groupVal = group.Key, 
                                       groupCount = group.Count() });

    // Columns of Result ListView
    listViewRes.Columns.Clear();
    listViewRes.Columns.Add("NAAM");
    listViewRes.Columns.Add("Total Count", 100);
    listViewRes.View = View.Details;

    // Add Result ListViewItems
    listViewRes.Items.AddRange(res.Select(g => new ListViewItem(new string[]
         {g.groupVal, 
          String.Format("(TOTAL {0})", g.groupCount.ToString())
         })).ToArray());
}

Note: According your example result you are interesting in Count and _not_ in Distinct Count.
I hope it helps.

Count total of distinguishable listview items

3 solutions

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0