Improve company productivity with a Business Account.Sign Up

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

DataSet Sum And Subtract 2 Columns?

I have a DataTable with 4 columns (ID, Product, QtySold, QtyBuy).
I would like to return Sum(QtySold) - Sum(QtyBuy). So far not working...

int ret = (from DataRow dr in dt.AsEnumerable()
                       where dr.Field<string>("Product") == "BEER"                      
                       && SUM(dr.Field<int>("QtySold")) - SUM(dr.Field<int>("QtyBuy"))
                       select ...;
       
        Label8.Text = "ret= "+ ret.ToString();
0
pointeman
Asked:
pointeman
  • 3
  • 2
  • 2
1 Solution
 
sjklein42Commented:
I think the problem may be that you've put the SUM...-SUM... expression in your "where" clause rather than the "select" part.

0
 
pointemanAuthor Commented:
Agreed, I don't know how to complete the code.
0
 
sjklein42Commented:
I think it may be as simple as this?

int ret = (from DataRow dr in dt.AsEnumerable() 
	where dr.Field<string>("Product") == "BEER" 
	select (SUM(dr.Field<int>("QtySold")) - SUM(dr.Field<int>("QtyBuy")));

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
pointemanAuthor Commented:
Don't have "Sum" in LINQ.
0
 
Fernando SotoRetiredCommented:
Hi pointeman;

If you are looking for the total amount BEER product remains then the query below will do that, the variable total will contain the value.

var total = ( from t in dt.AsEnumerable( )
              where t.Field<string>( "Product" ) == "BEER"
              group t by t.Field<string>( "Product" ) into beerGroup
              select beerGroup.Sum( b => b.Field<Int32>( "QtyBuy" ) - b.Field<Int32>( "QtySold" ) )
            ).FirstOrDefault( );  

Open in new window


Fernando
0
 
pointemanAuthor Commented:
The missing code: "Group t By ..."

Thank You...
0
 
Fernando SotoRetiredCommented:
Not a problem, glad I was able to help.  ;=)
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.

Join & Write a Comment

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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