Group By Rows and Total Rows

[yonialhadeff]

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]

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.