Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2066
  • Last Modified:

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

0
wraheem
Asked:
wraheem
  • 3
  • 2
1 Solution
 
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
 
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
Fernando SotoCommented:
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 SotoCommented:
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
 
Fernando SotoCommented:
Hi wraheem;

Have you tried my solution yet?

Fernando
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now