Solved

DataSet Sum And Subtract 2 Columns?

Posted on 2011-02-28
7
1,008 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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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

Suggested Solutions

IP addresses can be stored in a database in any of several ways.  These ways may vary based on the volume of the data.  I was dealing with quite a large amount of data for user authentication purpose, and needed a way to minimize the storage.   …
It seems a simple enough task, yet I see repeated questions asking how to do it: how to pass data between two forms. In this article, I will show you the different mechanisms available for you to do just that. This article is directed towards the .N…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

828 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