CODEWITHSUNDEEP
c#linq
patel.milanb
patel.milanb

Reputation: 5992

Comma separated values from SQL, getting distinct values and their count

I have this issue where i have a column in SQL table. the values are in comma separated format.

ex:

 1)   facebook,google,just giving, news letter, in-store
 2)   facebook,google,just giving
 3)   just giving
 4)   facebook
 5)   google
 6)   in-store,email
 7)   email,facebook

Now i want to query that table and get this values in a list where list contains distinct values and their count.

ex:

1) facebook - 10 
2) email - 20
3) in-store -5
and so on....

is there anyway to achieve this using LINQ?

NOTE: i can work on LINQ operators on LIST variable because my datasource which is 'entries' does not support LINQ( built in .NET 2.0)

        var results = new List<string>();
        var builder = new StringBuilder();

        foreach (var entry in entries.Cast<MWCompetitionsEntry>().Where(entry => entry.HowFound != null))
        {
              builder.Append(entry.HowFound).Append(",");
        }

        results.Add(builder.ToString());

RESULTS i am getting

facebook,email,in-store,google,in-store,newsletter,facebook,email,facebok

EDITS:

Now, I can work on results variable. i dont know how to get the unique values from that variable and their count.

please help

ANSWER

var values = new List<string>();
            var builder = new StringBuilder();
            var howFoundValues = new List<string>{"Email", "Facebook", "Twitter", "Leaflet", "Just Giving", "In-Store", "Other"};

            foreach (var entry in entries.Cast<MWCompetitionsEntry>().Where(entry => entry.HowFound != null))
            {
                  builder.Append(entry.HowFound).Append(",");
            }

            values.Add(builder.ToString());
            var result1 = values.SelectMany(l => l.Split(','))
                  .Where(howFoundValues.Contains) //will disregard any other items
                  .GroupBy(e => e)
                  .Select(e => String.Format("{0} - {1}",e.Key, e.Count()))
                  .ToList();

Upvotes: 0

Views: 1894

Answers (4)

Oleksii Aza
Oleksii Aza

Reputation: 5398

Something like this:

var groupedResults = results.SelectMany(v => v.Split(','))
                        .GroupBy(n => n)
                        .Select((item, index) => 
                                 new {
                                    name = String.Format("{0}) {1}", index+1, item.Key),
                                    count = item.Count()
                                 })
                        .ToList();
  1. You need to group elements by name.
  2. In LINQ to Objects .Select() operator can second paramater in lambda as index, that can help you to attach numeration.

Upvotes: 1

br1
br1

Reputation: 313

Try this

        Dictionary<string, int> valuesAndCount = new Dictionary<string, int>();
        foreach (var entry in entries) // entries is a collection of records from table
        {
            string[] values = entry.Split(',');
            foreach (var value in values)
            {
                if (!valuesAndCount.ContainsKey(value))
                {
                    valuesAndCount.Add(value, 0);
                }
                valuesAndCount[value] = valuesAndCount[value] + 1;
            }
        }


        //Then you'llhave your distinct values and thei count
        foreach (var key in valuesAndCount.Keys)
        {
            Console.WriteLine("{0} {1}",key, valuesAndCount[key]);
        }

Then the dictionary should contain your distinct values and their count

LINQ VERSION

        IEnumerable<string> strings = entries
            .ToList()
            .SelectMany(e => e.Split(','))
            .GroupBy(v => v)
            .Select(str => string.Format("{0} {1}", str.Count(), str.Key));


        foreach (var p in strings)
        {
            Console.WriteLine(p);
        }

Upvotes: 1

okrumnow
okrumnow

Reputation: 2416

var entries = new List<string>{"facebook,google,just giving, news letter, in-store",
 "facebook,google,just giving",
 "just giving", 
 "facebook",
 "google",
 "in-store,email",
 "email,facebook"};

var result = entries
.SelectMany (e => e.Split(','))
.GroupBy (e => e).Select (g => string.Format("{0} - {1}", g.Count (), g.Key));

result.Dump();

results in

4 - facebook 
3 - google 
3 - just giving 
1 -  news letter 
1 -  in-store 
1 - in-store 
2 - email

It's splitting yout entries first, then grouping by different strings.

If your data contains leading and/or trailing spaces like they do in your example, consider using .GroupBy(e => e.Trim())

Upvotes: 1

Bas
Bas

Reputation: 27095

How about:

var result = from word in entries.SelectMany(e => e.HowFound.Split(','))
             group word by word into gr
             select new {Entry = gr.Key, Count = gr.Count()};

grouping instead of distinct allows you to get the count.

Upvotes: 3

Related Questions