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

  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, Jan 23, 2007
    #2
    1. Advertisements

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.