[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Query a Dataset Sum with Group By

Posted on 2006-05-10
Medium Priority
Last Modified: 2009-07-29

I have a dataset and I need to create another dataset with the result of a query to the first dataset.

First dataset has three columns: id, code and amount   and I need a new dataset with the total amount for each id

SELECT  SUM(amount) AS total, id  FROM myTable GROUP BY id

But I don´t know how to query the first dataset

I hope it will be clear enough

Question by:PCNOTE
  • 2
  • 2
  • 2
LVL 34

Accepted Solution

Brian Crowe earned 500 total points
ID: 16653708
First I think you're confusing dataset and datatable.  A dataset is a collection of 1 or more datatables.

To answer your question you can't query datatables as you would a table in SQL Server or some other database.  You will have to calculate the sum "manually".

The pseudocode would look something like this...

Create a new datatable in your dataset (we'll call it "Summary")
Add the appropriate datacolumns ("ID", "TotalAmount")

Cycle through the rows in your detail datatable
   If there is a row in the summary table with the same id then just increment the totalamount value by the detail amount
   Otherwise, create a new row in the summary table and set the totalamount = detail amount

Author Comment

ID: 16653971
Ok,  I see

Now I have a new problem.

The table in the dataset has about 45000 rows and I need to send this dataset to Crystal Reports.

The problem is that it takes five minutes for Crystal to process such an amount of records. If I could filter the dataset with rows which have TotalAmount>0
(normally no more than 1000) I think it would be much faster.

Any idea?
LVL 34

Expert Comment

by:Brian Crowe
ID: 16655310
How are you pulling in the data originally?  Would it be possible to just summarize the data on the server side?
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 16658995

No, I'm afraid it isn't possible.

I create the original dataset querying a SQL Server database. This dataset must be processed afterwards with data input from the end-user. Depending on these inputs, I need a report to display only selected records (those with TotalAmount>0)

Expert Comment

ID: 21103055
there is a alternative to pseudocode  .

You can use LINQ.

A simple sample is

List<Schedule> schedules = new List<Schedule> {
                new Schedule(5311,      4 ,        "1/3/08",   "1/5/2008"),
                new Schedule(5311,      5 ,       " 1/3/08 ",   "1/5/2008"),
                new Schedule(9983,      1 ,        "1/3/08",   "1/5/2008"),
                new Schedule(9983,      2 ,        "1/3/08",   "1/5/2008"),
                new Schedule(5311,      3  ,       "1/7/08",   "1/12/2008"),
                new Schedule(5311,     7 ,        "1/8/08",   "1/12/2008")

            var groupById = from s in schedules
                        group s by s.empid into g
                        select g.AsEnumerable();

            IEnumerable<IEnumerable<Schedule>> result = new List<IEnumerable<Schedule>>();

            foreach (var item in groupById)
                var groupByWeekend = from s in item
                                     group s by s.weekending into g
                                     select g.AsEnumerable();
                result = result.Union(groupByWeekend);


            foreach (var item in result)
                Console.WriteLine("{0} {1} {2}", item.First().empid, item.First().Date, item.Sum(s => s.hours));


Too complex I think.


A trick one, but not always correct.


            var result =
                        from s in schedules
                        group s by  String.Concat("{0}_{1}", s.empid.ToString(), s.weekending.ToString()) into g
                        select g.AsEnumerable();


            foreach (var item in result)
                Console.WriteLine("{0} {1} {2}", item.First().empid, item.First().Date, item.Sum(s => s.hours));

you can use it for datatable and dataset.
Please ref. linq tutorials .

Rajendra dewani

Expert Comment

ID: 21103060

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

872 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