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

How to find sums of several columns using LINQ

wraheem
wraheem asked
on
Medium Priority
2,100 Views
Last Modified: 2013-11-11
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

Comment
Watch Question

Commented:
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)
                                           });

Author

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!

Commented:
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)
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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

Retired
CERTIFIED EXPERT
Distinguished Expert 2017
Commented:
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

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

Ask the Experts
Fernando SotoRetired
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
Hi wraheem;

Have you tried my solution yet?

Fernando
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

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

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.