Linq query to get count by status

Question

I want to count the items status wise in a datatable.

My Datatable looks like

Date , Tkt Status
1/2/13    open
1/2/13    open
2/2/13    open
2/2/13    closed
2/2/13    closed
3/3/13    in-progress
4/3/13    closed

I want another datatable with below data in the format

Date, Open, Closed, in-progress
1/2/13   2        0            0
2/2/13   1        2            0
3/3/13   0        0            1
4/3/13   0        1            0

I want it to be done using Linq.

My attempt so far

dataQuery.Query = "Yes";

                    dataQuery.ViewFields = "<FieldRef Name='Created' /><FieldRef Name='tckPrty' /><FieldRef Name='tckStat' />";
                    dataQuery.ViewFieldsOnly = true;
                    tktData = tktList.GetItems(dataQuery).GetDataTable();

                    var newData = from row in tktData.AsEnumerable()
                                  where groupDate(row.Field<string>("tckStat"))
                                          group row by row.Field<string>("Created") into Status
                                          orderby Status.Key
                                          select new
                                          {
                                              key = Status.Key,
                                              values = Status,
                                              count = Status.Count()
                                          };

                    foreach (var item in newData)
                    {
                        foreach (string s in tktStatus)
                        {
                            chartData.Rows.Add(item.key,item.count);
                        }
                    }

The function goes here

static bool groupDate(string skill) { bool value = false;

            if (skill== "open")
            {
                value = true;
            }
            else
            {
                value = false;
            }

            return value;
    }

I want a ferrari, a new house and a sixpack. I won't get any of these unless i put some work into it though - I suggest you attempt the solution, then ask for help on specific problems. — Takarii, Mar 04 '16 at 11:41
I have attempted the solution but I am stuck with making the status as columns and getting the count under it. i Will post my linq here. and dont judge people too early it might be that one does not want to confuse others by giving lots of codes at once. — Rehan, Mar 04 '16 at 11:46
It's not judgement. You stated in the question I want xyz. SO is not a code writing service, so if you provide nothing you will get nothing. see [Asking a good question](http://stackoverflow.com/help/how-to-ask) — Takarii, Mar 04 '16 at 11:49
if you know the solution then provide otherwise let other answer. and don't make it look dirty. — Rehan, Mar 04 '16 at 11:52

score 2 · Answer 1 · answered Mar 04 '16 at 12:06

Obviously we don't have your data table to work with, so I built an array from your example.

var lines = @"Date , Tkt Status
1/2/13    open
1/2/13    open
2/2/13    open
2/2/13    closed
2/2/13    closed
3/3/13    in-progress
4/3/13    closed".Split(new[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries).Skip(1).Select(l => l.Split(new[] { " " }, StringSplitOptions.RemoveEmptyEntries)).Select(x => new { Date = x[0], Status = x[1] }).ToArray();

Then, you can use LINQ like this to group it:

var grouped = lines.GroupBy(l => l.Date).Select(g => new {
    Date = g.Key,
    Open = g.Count(l => l.Status == "open"),
    Closed = g.Count(l => l.Status == "closed"),
    InProgress = g.Count(l => l.Status == "in-progress")
}).ToArray();

Output:

Date    Open Closed InProgress
1/2/13  2    0      0
2/2/13  1    2      0
3/3/13  0    0      1
4/3/13  0    1      0

Thanks a lot Keith. I will try the code and let you know. Though i have one issue that I really don't know what data can come in status column. It will be dynamic. And sceondly my Datatable is basically coming from Sharepoint list data having date and Status column. — Rehan, Mar 04 '16 at 12:23
@MdRehanMoazzam this answer might help you with dynamic columns: http://stackoverflow.com/a/26719396/4473405 — Keith Hall, Mar 04 '16 at 12:35

Linq query to get count by status

1 Answers1