Query a Dataset Sum with Group By


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

Who is Participating?
Brian CroweConnect With a Mentor Database AdministratorCommented:
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
PCNOTEAuthor Commented:
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?
Brian CroweDatabase AdministratorCommented:
How are you pulling in the data originally?  Would it be possible to just summarize the data on the server side?
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

PCNOTEAuthor Commented:

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)
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
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.

All Courses

From novice to tech pro — start learning today.