Click here to Skip to main content
14,326,142 members
Rate this:
Please Sign up or sign in to vote.
See more:
i have a table structure that looks that this

listed_companyid       numberof_units              userid
-----------------     -----------------        -----------------
       2                     4                        2
       2                     2                        2
       1                     6                        2
       5                     3                        3


i want to be able to achieve the result below with my query when i group by listed_companyid an userid
for userid = 2
total_unit = 12

additionally, i want to have something like this too
for userid =2
listed_companyid = 2 , total= 6
listed_companyid = 1 , total = 6

What I have tried:

var  listed = dbContext.listedCompanies.ToList();
    var stock = dbContext.stocks.Where(m=>m.userid == 2).ToList();
    var result = (from s in stock
                              join l in listed on s.listed_companyid equals l.id group s  by new { s.listed_companyid } into g select new
                              {
                                  g.Key,
                                  total_unit = g.Sum(s => s.numberof_units)
                              });
Posted
Updated 10-Jan-19 21:30pm
v2
Rate this:
Please Sign up or sign in to vote.

Solution 1

dbContext.YourTable.groupby(p => p.listed_compnyid).(p => p.sum(px => px.select(px.number_ofunits))).FirstOrDefault();
   
Rate this:
Please Sign up or sign in to vote.

Solution 2

Take a look at example:
var data = new[] {
	new {listed_companyid = 2, numberof_units= 4, userid =2},
	new {listed_companyid = 2, numberof_units= 2, userid =2},
	new {listed_companyid = 1, numberof_units= 6, userid =2},
	new {listed_companyid = 5, numberof_units= 3, userid =3}
	};

var result = data.GroupBy(x=>x.userid)
	.Select(grp=> new
    {
        userid = grp.Key,
        totalunits = grp.Sum(x=>x.numberof_units)
    })
    .ToList();
result.Dump();

var result2 = data.GroupBy(x=>x.userid)
	.Select(grp=> new
    {
        userid = grp.Key,
		details = grp.GroupBy(x=>x.listed_companyid)
			.Select(subgrp=> new
				{
					companyid = subgrp.Key,
					units = subgrp.Sum(y=>y.numberof_units) 
				})
			.ToList()
    })
    .ToList();
result2.Dump();


Result:
userid totalunits
2      12 
3      3


Result#2


useriddetails
companyidunits
226
16
353


Good luck!
   
v2

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

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100