How to find sums of several columns using LINQ

Hello All,

I have seen several posts concerning getting the sum of a column. I haven't seen any on how to get the sum of multiple columns.

I have a table that has multiple columns of money fields that I want to calculate & present on a label in an asp .net application. In the code I pasted I am able to return the sum for that one field...I have tried several "New WIth" combinations but they all die as soon as I try to add another column.

Any help will be appreciated.

Thanks!
Dim bx = (From bt In db.batches _
                Where bt.bOperator _
                Select bt.TotalPmnt).Sum

Open in new window

LVL 1
wraheemAsked:
Who is Participating?
 
Fernando SotoRetiredCommented:
Hi wraheem;

If you need to add more columns just follow the code sample. See code snippet to add another column Column4.

Fernando
Dim bx = From bt In db.batches _
         Where bt.bOperator = u _
         Group bt By bt.bOperator Into name = Group _
         Select col1 = name.Sum(Function(c1) CType(c1.Column1, Decimal)), _
                col2 = name.Sum(Function(c2) CType(c2.Column2, Decimal)), _
                col3 = name.Sum(Function(c3) CType(c3.Column3, Decimal)), _
                col4 = name.Sum(Function(c4) CType(c4.Column3, Decimal))
 
 
For Each b In bx
    Console.WriteLine(b.col1 & " - " & b.col2 & " - " & b.col3 & " - " & b.col4)
 
Next    

Open in new window

0
 
reb73Commented:
Adapt your LINQ statement as follows and continue on with other columns as required -

Dim bx = (From bt In db.batches _
                Where bt.bOperator _
                select new
                                           {
                                               Col1Sum = bt.Sum(a => a.Col1),
                                               Col2Sum = bt.Sum(b => b.Col2)
                                           });
0
 
wraheemAuthor Commented:
Reb73,

Thanks for the quick reply; however I'm still having issues. I don't know if it matters but the where line in my original code should've been where bt.bOperator = u (which is dim'ed as a string & set to the user name). I don't think that matters to why I can't still run it though.

It seems as though it's not "recongnizing" what's going on because as soon as I get to a =>a. intellesense takes over & throws AddressOf in there...it does the same for all other letters I try using too.

Is this because your code looks like c#? I noticed a warning/error at new saying "Type or With expected"...

Thanks Again!
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
reb73Commented:
Apologies, it was indeed partial C# syntax (my mistake!). For VB.net, try -

Dim bx = (From bt In db.batches _
                Where bt.bOperator = u _
                Into _
                Col1Sum = Sum(bt.Col1), _
                Col2Sum = Sum(bt.Col1) _
                Select _
                   Col1Sum, _
                   Col2Sum)
0
 
Fernando SotoRetiredCommented:
Hi wraheem;

This code snippet will sum up the individual columns and return the totals for each column for the value of the user u.

Let me know if this is what you are looking for.

Fernando

' Assign the user name to be used in the where clause.
Dim u As String = "UserName"
 
Dim bx = From bt In db.batches _
         Where bt.bOperator = u _
         Group bt By bt.bOperator Into name = Group _
         Select col1 = name.Sum(Function(c1) CType(c1.Column1, Decimal)), _
                col2 = name.Sum(Function(c2) CType(c2.Column2, Decimal)), _
                col3 = name.Sum(Function(c3) CType(c3.Column3, Decimal))
 
For Each b In bx
    Console.WriteLine(b.col1 & " - " & b.col2 & " - " & b.col3)
Next    

Open in new window

0
 
Fernando SotoRetiredCommented:
Hi wraheem;

Have you tried my solution yet?

Fernando
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.