?
Solved

DataSet Sum And Subtract 2 Columns?

Posted on 2011-02-28
7
Medium Priority
?
1,025 Views
Last Modified: 2013-11-11
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
Comment
Question by:pointeman
  • 3
  • 2
  • 2
7 Comments
 
LVL 16

Expert Comment

by:sjklein42
ID: 34997627
I think the problem may be that you've put the SUM...-SUM... expression in your "where" clause rather than the "select" part.

0
 

Author Comment

by:pointeman
ID: 34998772
Agreed, I don't know how to complete the code.
0
 
LVL 16

Expert Comment

by:sjklein42
ID: 34999084
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 

Author Comment

by:pointeman
ID: 34999432
Don't have "Sum" in LINQ.
0
 
LVL 64

Accepted Solution

by:
Fernando Soto earned 2000 total points
ID: 34999530
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
 

Author Closing Comment

by:pointeman
ID: 35000547
The missing code: "Group t By ..."

Thank You...
0
 
LVL 64

Expert Comment

by:Fernando Soto
ID: 35000620
Not a problem, glad I was able to help.  ;=)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Integration Management Part 2
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question