If you go the [LINQ.js][1] route, you can do it like this:



    var aggregatedObject = Enumerable.From(dataArray)
            .GroupBy("$.category", null,
                     function (key, g) {
                         return {
                           category: key,
                           hits: g.Sum("$.hits"),
                           bytes: g.Sum("$.bytes")
                         }
            })
            .ToArray();

### Working demo with Stack Snippets:





    var dataArray = [ 
      { category: "Search Engines", hits: 5, bytes: 50189 },
      { category: "Content Server", hits: 1, bytes: 17308 },
      { category: "Content Server", hits: 1, bytes: 47412 },
      { category: "Search Engines", hits: 1, bytes: 7601  },
      { category: "Business",       hits: 1, bytes: 2847  },
      { category: "Content Server", hits: 1, bytes: 24210 },
      { category: "Internet ",      hits: 1, bytes: 3690  },
      { category: "Search Engines", hits: 6, bytes: 613036 },
      { category: "Search Engines", hits: 1, bytes: 2858  } 
    ];

    var aggregatedObject = Enumerable.From(dataArray)
            .GroupBy("$.category", null,
                     function (key, g) {
                         return {
                           category: key,
                           hits: g.Sum("$.hits"),
                           bytes: g.Sum("$.bytes")
                         }
            })
            .ToArray();

    console.log(aggregatedObject);



    <script src="//cdnjs.cloudflare.com/ajax/libs/linq.js/2.2.0.2/linq.min.js"></script>



Also, you can find more info on [linqjs group by with a sum][2]

[1]: http://linqjs.codeplex.com/
[2]: https://stackoverflow.com/q/17824008/1366033

This is my first attempt at doing JavaScript with some JSON data objects and need some advice on the proper way to attain my goal.

Some server-side code actually generates a JSON formatted string that I have to work with and assign it to a string:

    var dataString='$DATASTRING$';

But the end-result I have to work with after the server substitutes its data (without the \r\n, of course):


    var dataString='[ 
     { "category" : "Search Engines", "hits" : 5, "bytes" : 50189 },
     { "category" : "Content Server", "hits" : 1, "bytes" : 17308 },
     { "category" : "Content Server", "hits" : 1, "bytes" : 47412 },
     { "category" : "Search Engines", "hits" : 1, "bytes" : 7601 },
     { "category" : "Business", "hits" : 1, "bytes" : 2847 },
     { "category" : "Content Server", "hits" : 1, "bytes" : 24210 },
     { "category" : "Internet Services", "hits" : 1, "bytes" : 3690 },
     { "category" : "Search Engines", "hits" : 6, "bytes" : 613036 },
     { "category" : "Search Engines", "hits" : 1, "bytes" : 2858 } 
    ]';
 
And then I can change it to an object to work with.

    var dataObject=eval("("+dataString+")");
    

This allows me to access the data individual rows of data, but I need to sum, group by, and order the values.

I need to the equivalent of an SQL statement like this:

    SELECT category, sum(hits), sum(bytes) 
    FROM dataObject
    GROUP BY category
    ORDER BY sum(bytes) DESC

My desired output would be an object like this that I can further process:

    var aggregatedObject='[ 
     { "category" : "Search Engines", "hits" : 13, "bytes" : 673684 },
     { "category" : "Content Server", "hits" : 3, "bytes" : 88930 },
     { "category" : "Internet Services", "hits" : 1, "bytes" : 3690 },
     { "category" : "Business", "hits" : 1, "bytes" : 2847 } 
    ]';


...but i don't know where to start.

I could loop through all the category values and find the unique categories first, then loop again and sum the hits and bytes, then again to sort, but it seems there has got to be an easier way.

prototype.js (1.7) is already included on the client page, but I could add Underscore, jQuery, or some other small library if I had to.

I just don't know what would be best, easiest, smallest with the least amount of code to process the query.

Any suggestions?




JavaScript SUM and GROUP BY of JSON data

just exclude those group ID's that have a record where HasData = 0.

    select distinct a.groupID
    from table1 a 
    where not exists(select * from table1 b where b.HasData = 0 and b.groupID = a.groupID)



I'd like to do a query for every GroupID (which always come in pairs) in which both entries have a value of 1 for HasData.

	|GroupID | HasData |
	|--------|---------|
	|  1     |  1      |
	|  1     |  1      |
	|  2     |  0      |
	|  2     |  1      |
	|  3     |  0      |
	|  3     |  0      |
	|  4     |  1      |
	|  4     |  1      |

So the result would be:

    1
    4

here's what I'm trying, but I can't seem to get it right.  Whenever I do a `GROUP BY` on the GroupID then I only have access to that in the selector



    SELECT GroupID
    FROM Table
    GROUP BY GroupID, HasData
    HAVING SUM(HasData) = 2 

But I get the following error message because HasData is acutally a bit:  

    Operand data type bit is invalid for sum operator.

Can I do a count of two where both records are true?

Finding records sets with GROUP BY and SUM

Try using this in LinqPad, and subbing out for your database entity it should get you closer. 

	Public Sub grouper2()
		Dim numbers = New Integer() {1,1,2,2,2,3}
	
		Dim numberGroups = From w In numbers _
						Group w By Key = w Into Group _
						Select Number = Key, numbersCount = Group.Count()
	
		'linqpad specific output
		'numberGroups.Dump()
		
		For Each g In numberGroups
			Console.WriteLine("Numbers that match '{0}':", g.Number)
				Console.WriteLine(g.numbersCount)        
		Next
	
	End Sub


I'm trying to use a LINQ query to determine how many of each particular object type I have and record those values into an anonymous type.

Let's say I have some data that looks like this (there are really objects exposing this property, but it'll work the same)

    GroupId
    1
    1
    2
    2
    2
    3

I know how to format my query in SQL.  It would be something like this:



    SELECT grp = GroupId, cnt = COUNT(*)
    FROM myTable
    GROUP BY GroupId

In this case the output would be something like [this SQL Fiddle][1]:

    GroupID  Count
    1        2
    2        3
    3        1

How can I do the same thing with LINQ in vb.net



    Dim groupCounts = From person In data
                      Group By person.GroupId
                      Select new {group = person.GroupId, count = count(*)}

That's not quite right, but I think it's close.

Also, not knowing much about anonymous types, can I actually declare `groupCounts` ahead of time that it will be an enumeration of items which each have a group and count property?

[1]: http://sqlfiddle.com/#!9/ef3427/1

LINQ query with GROUP BY and Count(*) into Anonymous Type

If you want to abstract away some of the logic into a reusable extension method, you can add the following:


```cs
public static IEnumerable<TSource> DistinctBy<TSource, TKey>
    (this IEnumerable<TSource> source, Func<TSource, TKey> keySelector)
{
    HashSet<TKey> seenKeys = new HashSet<TKey>();
    foreach (TSource element in source)
    {
        if (!seenKeys.Contains(keySelector(element)))
        {
            seenKeys.Add(keySelector(element));
            yield return element;
        }
    }
}
```

This will work for both single properties and composite properties and return the first
matching element

```cs
// distinct by single property
var productsByTitle = animals.DistinctBy(a => a.Title);

// distinct by multiple properties
var productsByTitleAndColor = animals.DistinctBy(a => new { a.Title, a.Color} );
```

One benefit to this approach (instead of group by + first) is you can return a yieldable enumerable in case you have later criteria that don't force you to loop through the entire collection

**Further Reading**: [linq query to return distinct field values from a list of objects][1]

[1]: https://stackoverflow.com/a/35795985/1366033


How can I using c# and Linq to get a `result` from the next list:

     var pr = new List<Product>()
       {
           new Product() {Title="Boots",Color="Red",    Price=1},
           new Product() {Title="Boots",Color="Green",  Price=1},
           new Product() {Title="Boots",Color="Black",  Price=2},
         
           new Product() {Title="Sword",Color="Gray", Price=2},
           new Product() {Title="Sword",Color="Green",Price=2}
       };

 
  `Result`:

            {Title="Boots",Color="Red",  Price=1},               
            {Title="Boots",Color="Black",  Price=2},             
            {Title="Sword",Color="Gray", Price=2}
               

I know that I should use `GroupBy` or `Distinct`, but understand how to get what is needed
 

       List<Product> result = pr.GroupBy(g => g.Title, g.Price).ToList(); //not working
       List<Product> result  = pr.Distinct(...);
Please help