Query a Dataset Sum with Group By

Posted on 2006-05-10
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
    LVL 34

    Accepted Solution

    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

    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
    How are you pulling in the data originally?  Would it be possible to just summarize the data on the server side?

    Author Comment


    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)
    LVL 3

    Expert Comment

    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
    LVL 3

    Expert Comment


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Article by: Kraeven
    Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
    If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now