Solved

DataSet Sum And Subtract 2 Columns?

Posted on 2011-02-28
7
1,007 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

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

Accepted Solution

by:
Fernando Soto earned 500 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 63

Expert Comment

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

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

821 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