Click here to Skip to main content
15,909,737 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hi there,
I need to count the number of rows if there is a duplicate value in a specific column
for example

Name | Duration
John | 20
Mary | 40
Lena | 50
John | 10
John | 20
Mary | 10

i want the results to be this way
Name | vol | Longest
John | 3 | 20
Mary | 2 | 40
Lena | 1 | 50

im using access database. I put the data in a datatable. after calculating what i want i will insert it into new database...
is there any way i can achieve this?
Thanks for you help in advance
Posted
Comments
Herman<T>.Instance 26-Mar-12 4:08am    
do you need SQl of LINQ code?
Member 8642100 27-Mar-12 6:10am    
LINQ...Thank you...

Try:
SQL
SELECT Name, COUNT(Duration) as Vol, MAX(Duration) as Longest FROM MyTable Group by Name
 
Share this answer
 
Comments
ProEnggSoft 26-Mar-12 4:13am    
+5
Member 8642100 26-Mar-12 4:24am    
thanks for your reply.
i try that using the datatable select statement?
OriginalGriff 26-Mar-12 4:36am    
Yes.
Member 8642100 26-Mar-12 5:07am    
i'mm sorry but how do i achieve that?
OriginalGriff 26-Mar-12 6:43am    
Depends on how you load your data tabke, or use your DB - I would need to see the relevant code fragments.
select Name,count(Duration)as vol, max(Duration) as Longest from TableName group by Name
 
Share this answer
 
Comments
Member 8642100 26-Mar-12 5:47am    
i'm sorry but can i have the full code?
XML
foreach (DataRow row in dt.Rows)
{
   var nameCo = from c in dt.AsEnumerable()
                group c by c.Field<string>("yName") into g
                 select new
                 {
                   Name = g.Key,
                   List = g.ToList(),
                 } into g
                 select new
                 {
                   g.Name,
                   Count = g.List.Count,
                   durAve = g.List.Average(x => x.Field<int>("Duration")),
                   durMax = g.List.Max(y => y.Field<int>("Duration"))
                 };
        foreach (var summary in nameCo)
        {
          Console.WriteLine("Name:" + summary.Name.ToString() + ", Volume:" +  summary.Count.ToString() + ",Average:" + summary.durAve.ToString() + "Longest duration: " + summary.durMax.ToString());
        }
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900