CODEWITHSUNDEEP
c#
hanzi_ru
hanzi_ru

Reputation: 153

Add number of duplicate rows in a datatable to a new column

I need to identify the duplicate columns in the table and add a new column with the number of duplicate column. Thanks in advance Here is the table that needs to be edited.

enter image description here

resultant table:

enter image description here

Upvotes: 0

Views: 1920

Answers (4)

Martin Milan
Martin Milan

Reputation: 6390

If you want LinQ, and remembering to include using System.Linq; at the top of your file, try this:

class Program
    {
        public static void Main(string[] args)
        {
            Console.WriteLine("Hello World!");

            string[] data = new String[] {"john","john","mike","ann","ann","ann"};

            var results = data.GroupBy(x => x).Select(g => new {name = g.Key, count = g.Count()});

            foreach (var result in results)
            {
                Console.WriteLine("{0} occurred {1} times...", result.name, result.count);
            }


            Console.Write("Press any key to continue . . . ");
            Console.ReadKey(true);
        }
    }

Upvotes: 1

Andris
Andris

Reputation: 1948

In LINQ:

table.GroupBy (t => t.Name).Select (t => new {Name = t.Key, Count = t.Count ()})

Assuming:

  • table is a collection that contains your first table
  • Name is the name of the column that contains the names

Upvotes: 0

Amit Tiwari
Amit Tiwari

Reputation: 368

you can try this

void tables()
    {
        DataTable dt = new DataTable();
        dt.Columns.AddRange(new DataColumn[1] { new DataColumn("Name") });
        dt.Rows.Add("John");
        dt.Rows.Add("Mike");
        dt.Rows.Add("Ann");
        dt.Rows.Add("Sam");
        DataTable dt1 = new DataTable();
        dt1.Columns.AddRange(new DataColumn[1] { new DataColumn("Name") });
        dt1.Rows.Add("John");
        dt1.Rows.Add("John");
        dt1.Rows.Add("Mike");
        dt1.Rows.Add("Ann");
        dt1.Rows.Add("Ann");
        dt1.Rows.Add("Ann");
        DataTable dt2 = new DataTable();
        dt2.Columns.AddRange(new DataColumn[3] { new DataColumn("Name"), new DataColumn("Available"), new DataColumn("Count") });
        int count = 0;
        foreach (DataRow r in dt.Rows)
        {
            count = dt1.Select("Name='" + r[0].ToString() + "'").Count();
            if (count > 0)
                dt2.Rows.Add(r[0].ToString(), "available", count.ToString());
            else
                dt2.Rows.Add(r[0].ToString(), "unavailable", count.ToString());
        }
    }

Upvotes: 0

rajeemcariazo
rajeemcariazo

Reputation: 2524

In SQL, try something like this:

SELECT FirstName, COUNT(*) FROM FirstNames GROUP BY FirstName

Upvotes: 1

Related Questions