count by distinct values from a datatable RSS

• • Reply
  

  itsjp

  Member

  24 Points

  103 Posts

  count by distinct values from a datatable

  Nov 03, 2012 03:10 AM|LINK

  Hi,

  I have following datatable in my vb.net web page

   Name...........Category.........Subcategory..........subcategory1 

  Kiss...........Rock.............Glam Rock  ......................HardRock

  Metallica......Rock.............Hard Rock  ...............Soft Rock

  Bon Jovi.......Rock.............Soft Rock  .................Glam Rock

  Slade..........Rock.............Glam Rock  ....................Soft Rock

  Meatloaf.......Rock.............Soft Rock  .....................Rock1

  Wilee..........Dance............Grime ........................Hard Rock 

  Mgmt...........Dance............Nu Rave  .....................Grime

  Dizee..........Dance............Soft Rock  ..........................Neu Raj

  Now I want to get count of each category/subcategory count like folllowing

  Subcategories.......Count 

  Glam Rock.........3

  Hard Rock.........3

  Soft  Rock........4    

  Grime.............2 

  Nu Rave...........1

  Neu Raj............1

  I dont want to loop through each row of datatable since it contains large volume of data ....10,000 and 1000s'  of data.......I want a LINQ query or any other way to achieve this....

  can anyone please help me.....

   

• • Reply
  

  karthicks

  All-Star

  31382 Points

  5424 Posts

  Re: count by distinct values from a datatable

  Nov 03, 2012 04:59 AM|LINK

  hi, refer below code, here i have used both LINQ and select , you can choose your choice

  protected void Page_Load(object sender, EventArgs e)
  	{
  		DataTable dt = new DataTable();
  		dt.Columns.Add("Name");
  		dt.Columns.Add("Category");
  		dt.Columns.Add("Subcategory");
  		dt.Columns.Add("subcategory1");
  		dt.Rows.Add(new Object[] { "Kiss", "Rock", "Glam Rock", "Hard Rock" });
  		dt.Rows.Add(new Object[] { "Metallica", "Rock", "Hard Rock", "Soft Rock" });
  		dt.Rows.Add(new Object[] { "Bon Jovi", "Rock", "Soft Rock", "Glam Rock" });
  		dt.Rows.Add(new Object[] { "Slade", "Rock", "Glam Rock", "Soft Rock" });
  		dt.Rows.Add(new Object[] { "Meatloaf", "Rock", "Soft Rock", "Rock1" });
  		dt.Rows.Add(new Object[] { "Wilee", "Dance", "Grime", "Hard Rock" });
  		dt.Rows.Add(new Object[] { "Mgmt", "Dance", "Nu Rave", "Grime" });
  		dt.Rows.Add(new Object[] { "Dizee", "Dance", "Soft Rock", "Neu Raj" });
  
  		Response.Write("Glam Rock :" + GetCount(dt, "Glam Rock"));
  		Response.Write("</br>");
  		Response.Write("Hard Rock :" + GetCount(dt, "Hard Rock"));
  		Response.Write("</br>");
  		Response.Write("Soft Rock :" + GetCount(dt, "Soft Rock"));
  		Response.Write("</br>");
  		Response.Write("Grime :" + GetCount(dt, "Grime"));
  		Response.Write("</br>");
  		Response.Write("Nu Rave :" + GetCount(dt, "Nu Rave"));
  		Response.Write("</br>");
  		Response.Write("Neu Raj :" + GetCount(dt, "Neu Raj"));
  		Response.Write("</br>");
  
  		Response.Write("Glam Rock :" + GetCountUsingSelect(dt, "Glam Rock"));
  		Response.Write("</br>");
  		Response.Write("Hard Rock :" + GetCountUsingSelect(dt, "Hard Rock"));
  		Response.Write("</br>");
  		Response.Write("Soft Rock :" + GetCountUsingSelect(dt, "Soft Rock"));
  		Response.Write("</br>");
  		Response.Write("Grime :" + GetCountUsingSelect(dt, "Grime"));
  		Response.Write("</br>");
  		Response.Write("Nu Rave :" + GetCountUsingSelect(dt, "Nu Rave"));
  		Response.Write("</br>");
  		Response.Write("Neu Raj :" + GetCountUsingSelect(dt, "Neu Raj"));
  		Response.Write("</br>");
  	}
  
  	private int GetCount(DataTable dt, string value)
  	{
  		int count = (from dr in dt.AsEnumerable()
  								 where dr["Subcategory"].ToString().Equals(value, StringComparison.CurrentCultureIgnoreCase)
  									 || dr["subcategory1"].ToString().Equals(value, StringComparison.CurrentCultureIgnoreCase)
  								 select dr).Count();
  
  		return count;
  	}
  
  	private int GetCountUsingSelect(DataTable dt, string value)
  	{
  		int count = dt.Select("Subcategory='" + value + "' or subcategory1='" + value + "'").Count();
  		return count;
  	}

  Thanks,
  Karthick S
  • Marked as answer by Mark - MSFT on Nov 09, 2012 10:16 AM
• • Reply
  

  sureshkumar....

  Contributor

  2143 Points

  504 Posts

  Re: count by distinct values from a datatable

  Nov 03, 2012 06:16 AM|LINK

  You can do that using the below query

  select count(*),Subcategory from yourtable group by Subcategory

  Please click "Mark as Answer" if you think this post answer your question
  
  Best Regards,
  Suresh Kumar Gundala
• • Reply
  

  vickyasp.net

  Member

  570 Points

  425 Posts

  Re: count by distinct values from a datatable

  Nov 03, 2012 06:46 AM|LINK

  http://www.w3schools.com/sql/sql_distinct.asp

  http://weblogs.sqlteam.com/jeffs/archive/2007/12/13/select-distinct-order-by-error.aspx

  http://stackoverflow.com/questions/54418/how-do-i-or-can-i-select-distinct-on-multiple-columns-postgresql

  Thanks & regards
  Vignesh
  
  
  • Marked as answer by Mark - MSFT on Nov 09, 2012 10:16 AM
• • Reply
  

  ramiramilu

  All-Star

  95503 Points

  14106 Posts

  Re: count by distinct values from a datatable

  Nov 03, 2012 08:48 AM|LINK

  use Group By with Count in LINQ...somethig like - http://stackoverflow.com/questions/3829129/use-linq-to-group-data-from-datatable

  Thanks,

  Website
  JumpStart
• • Reply
  

  itsjp

  Member

  24 Points

  103 Posts

  Re: count by distinct values from a datatable

  Nov 03, 2012 10:40 AM|LINK

  Dim counts = dt.AsEnumerable().GroupBy(Function(i) i.Field(Of String)("SubCategory")).Select(Function(g) g.Count())

  but here I am getting only the count of rows with Subcategory values exists in that partocular column only

  means for example in the above example  I have the first category item "Glam Rock" , and is exists in subcategory columns for 2 rows and also exists in Subcategory1 columns for 1 row.......So the count should be 3

  But with above LINQ query I am getting only count 2....

  how can I search for the values in multiple columns and get the count??? (and also if the row contains Subcategory and Subcategory1 as "Glam Rock" the count should be 1 not 2....means it is counting the ID where the values exists in columns and displays the row count for all the column values)

• • Reply
  

  raghavendra ...

  Participant

  1890 Points

  435 Posts

  Re: count by distinct values from a datatable

  Nov 03, 2012 10:58 AM|LINK

  Use following query to get the result ..! 

  SELECT COUNT(*) AS   Category _count , Category FROM tbl_name GROUP BY  Category
  
  SELECT COUNT(*) AS  Subcategory _count , Subcategory FROM tbl_name GROUP BY  Subcategory

   

• • Reply
  

  RameshRajend...

  Star

  7983 Points

  2099 Posts

  Re: count by distinct values from a datatable

  Nov 03, 2012 12:21 PM|LINK

  Hai

  Try this way

  DataView view = new DataView(table);

  DataTable distinctValues = view.ToTable(true, "Column1", "Column2" ...);

  or 

  check anothery way

  http://social.msdn.microsoft.com/Forums/en-AU/vbgeneral/thread/0c6a315b-6514-448a-9c30-32f43f31e7a8