Group By Rows and Total Rows

Discussion in 'ASP .Net Datagrid Control' started by yonialhadeff, Jan 23, 2007.

  1. yonialhadeff

    yonialhadeff Guest

    Hello,

    I have a datagrid wich is fill in by a query that has a group by.

    I want to have a row for each element of the field that is in the group
    by
    lets have an example

    SELECT TOWN FROM AllTownInTheWorlds GROUP BY Country

    Town NbofHabitant Superficies
    BELGIUM
    Brrussels 10
    Bruges 4
    Antwerp 7
    TOTAL 21
    France
    Paris 20
    Lyon 14
    Lille 9
    TOTAL 43
    England
    London 15
    Total 15
    .....

    Is that possible ?

    Do you know a tutorial that explain it ?
    Can you explain me how to do it ?

    Thank you in advance for your help

    Yoni
    yonialhadeff, Jan 23, 2007
    #1
    1. Advertising

  2. Yoni,

    It will be a good excercise.

    First of all your select is not going to work since column TOWN is not
    contained in the group by clause. You need something like
    SELECT Country, TOWN, NbofHabitant, Superficies FROM AllTownInTheWorlds
    order by Country, TOWN

    It is possible to achieve what you want if you use a repeater rather than a
    gridview:

    <table>
    <tr>
    <td>Town</td>
    <td>NbofHabitant</td>
    <td>Superficies</td>
    </tr>
    <asp:repeater runat=server id=myRepeater datasource=myDataSet>
    <itemtemplate>
    <tr runat=server id=trFirst>
    <td colspan=3><%# Eval("Country") %></td>
    </tr>
    <tr runat=server id=trSecond>
    <td><%# Eval("Town") %></td>
    <td><%# Eval("NbofHabitant") %></td>
    <td><%# Eval("Superficies") %></td>
    </tr>
    <tr runat=server id=trThird>
    <td>Total</td>
    <td colspan=2><asp:Label runat=server id=lblTotal /></td>
    </tr>
    </itemtemplate>
    </asp:repeater>
    </table>

    Databind the repeater. You will get 3 rows for every town . Handle PreRender
    event. In the event loop through the rows and hide/show rows for every town
    with Visible property. If it is the first town for the country, show trFirst
    and trSecond and hide trThird. For the last town for a country show also
    trThird. For the towns between the first and the last show only trSecond.

    In the same loop through the items you can get values of NbofHabitant
    columns and count the totals.

    --
    Eliyahu Goldin,
    Software Developer & Consultant
    Microsoft MVP [ASP.NET]
    http://msmvps.com/blogs/egoldin


    "yonialhadeff" <> wrote in message
    news:...
    > Hello,
    >
    > I have a datagrid wich is fill in by a query that has a group by.
    >
    > I want to have a row for each element of the field that is in the group
    > by
    > lets have an example
    >
    > SELECT TOWN FROM AllTownInTheWorlds GROUP BY Country
    >
    > Town NbofHabitant Superficies
    > BELGIUM
    > Brrussels 10
    > Bruges 4
    > Antwerp 7
    > TOTAL 21
    > France
    > Paris 20
    > Lyon 14
    > Lille 9
    > TOTAL 43
    > England
    > London 15
    > Total 15
    > ....
    >
    > Is that possible ?
    >
    > Do you know a tutorial that explain it ?
    > Can you explain me how to do it ?
    >
    > Thank you in advance for your help
    >
    > Yoni
    >
    Eliyahu Goldin, Jan 23, 2007
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. =?Utf-8?B?ZGF2aWQ=?=

    How to get the total number of rows in a Dataset?

    =?Utf-8?B?ZGF2aWQ=?=, May 3, 2005, in forum: ASP .Net
    Replies:
    1
    Views:
    17,286
    Yunus Emre ALPĂ–ZEN [MCAD.NET]
    May 3, 2005
  2. thomson
    Replies:
    1
    Views:
    793
    Brock Allen
    Jun 11, 2005
  3. Praveen homkar via JavaKB.com

    how to get total number of rows contained in a resultset

    Praveen homkar via JavaKB.com, Aug 5, 2005, in forum: Java
    Replies:
    1
    Views:
    5,331
  4. Craig Burkett
    Replies:
    2
    Views:
    620
    Craig Burkett
    Sep 11, 2003
  5. Rajani

    group total in a report

    Rajani, Feb 26, 2005, in forum: ASP General
    Replies:
    1
    Views:
    136
    Bob Barrows [MVP]
    Feb 26, 2005
Loading...

Share This Page