We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

# Ling to SQL C# Group By

on
Medium Priority
1,576 Views
This is what I have.....

DailyHoursID
1
2
3

Table2 HoursTimeCategory
DailyHoursID     TimeCategory         Hours     Min
1                         0                              8          30
1                         1                              9          0
2                         0                              6          30

How do I get a result set of the bellow using LINQ
TimeCategory     Hours   Min
0                          14        60
1                          9          0

Im only able to get the Hours at the moment with the following. ........
How do Get the sum of Hours & Min ?

``````var rsDailyHoursTC = (from d in Empctx.DailyHoursTimeCategories
join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
where empCatList.Contains(d.DailyHoursID)
group d.Hours by tc.TimeCategoryName into TotalArea
select new
{
Category = TotalArea.Key,
Hours = TotalArea.Sum()

});
``````
Comment
Watch Question

## View Solutions Only

Commented:
Can you try this -

``````HoursTC = (from d in Empctx.DailyHoursTimeCategories
join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
where empCatList.Contains(d.DailyHoursID)
group d.Hours + d.Min by tc.TimeCategoryName into TotalArea
select new
{
Category = TotalArea.Key,
Hours = TotalArea.Sum()

});
``````

Commented:
Not working get zeros back.....
I need to sum the Hour & Minutes columns.
Hours = TotalArea.Sum()

Commented:
var rsDailyMinutesTC = (from d in Empctx.DailyHoursTimeCategories
join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
where empCatList.Contains(d.DailyHoursID) && d.Minutes != null
group d.Hours + d.Minutes by tc.TimeCategoryName into TotalArea
select new
{
Category = TotalArea.Key,
Hours = TotalTimeHours,
Minutes = TotalTimeMinutes

});

Commented:
``````HoursTC = (from d in Empctx.DailyHoursTimeCategories
join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
where empCatList.Contains(d.DailyHoursID)
group d.Hours by tc.TimeCategoryName into TotalArea
group d.Min by tc.TimeCategoryName into TotalMins
select new
{
Category = TotalArea.Key,
Hours = TotalArea.Sum()
Mins  = TotalMins.Sum()
});
``````

Commented:
Double groups not allowed.
Commented:
Try this -
``````HoursTC = (from d in Empctx.DailyHoursTimeCategories
join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
where empCatList.Contains(d.DailyHoursID)
group d by tc.TimeCategoryName into TotalArea
select new
{
Category = TotalArea.Key,
Hours = TotalArea.Sum(TotalArea => TotalArea.Hours)
Mins  = TotalMins.Sum(TotalArea => TotalArea.Min)
});
``````

Not the solution you were looking for? Getting a personalized solution is easy.

Retired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi u2envy1;

I used Table1 and Table2 in my query cause I was not sure from your question, see code snippet.

Fernando
``````THIS IS Table1 IN THE QUERY    THIS IS Table2 IN THE QUERY
DailyHoursID                   DailyHoursID     TimeCategory         Hours     Min
1                              1                0                    8         30
2                              1                1                    9         0
3                              2                0                    6         30

var rsDailyHoursTC = from t1 in Empctx.Table1
join t2 in Empctx.Table2 on t1.DailyHoursID equals t2.DailyHoursID
into groupedRec
where groupedRec.Any()
select new
{
t1.DailyHoursID,
totalHours = groupedRec.Sum(h => h.Hours),
totalMin = groupedRec.Sum(m => m.Min)
};

foreach (var rec in rsDailyHoursTC)
{
Console.WriteLine("Catagory = {0} - Hours = {1} - Min = {2}",
rec.DailyHoursID, rec.totalHours, rec.totalMin);
}

// Results of running the code with the test data.
Catagory = 1 - Hours = 17 - Min = 30
Catagory = 2 - Hours =  6 - Min = 30
``````

Commented:
reb73:
TotalMins is not defined....
Mins  = TotalMins.Sum(TotalArea => TotalArea.Min)

FernandoSoto:
Bellow is the SQL code I would like to duplicate in Linq.
Im getting errors on

(h => h.Hours)
(m => m.Min)

``````SELECT     TimeCategory.TimeCategoryName, Sum(DailyHoursTimeCategories.Hours) AS [Hours] , Sum(DailyHoursTimeCategories.Minutes) AS [Minutes]
FROM         DailyHours INNER JOIN
DailyHoursTimeCategories ON DailyHours.DailyHoursID = DailyHoursTimeCategories.DailyHoursID INNER JOIN
TimeCategory ON DailyHoursTimeCategories.TimeCategoryID = TimeCategory.TimeCategoryID
GROUP BY TimeCategory.TimeCategoryName
``````
Commented:
That was typo. Please change the line as follows  -

Mins  = TotalArea.Sum(TotalArea => TotalArea.Minutes)

Commented:
Thx peeps.......
All your efforts is much appreciated reb73.
It is working !!
var rsDailyHoursTC2 = (from d in Empctx.DailyHoursTimeCategories
join tc in Empctx.TimeCategories on d.TimeCategoryID equals tc.TimeCategoryID
where empCatList.Contains(d.DailyHoursID)
group d by tc.TimeCategoryName into TotalArea
select new
{
Category = TotalArea.Key,
Hours = TotalArea.Sum(h => h.Hours),
Mins = TotalArea.Sum(m => m.Minutes)
});

Commented:
Thx
##### Thanks for using Experts Exchange.

• View three pieces of content (articles, solutions, posts, and videos)
• Ask the experts questions (counted toward content limit)
• Customize your dashboard and profile