Total by deparment in different column

Question

User1414062701 posted

Guys I have a situation here as below:

Department    |    Cost Centre    |     Balance   |     Total

    HR                         CC1               Value 1          Value 1 + Value 2 + Value 3

                                 CC2               Value 2

                                 CC3               Value 3

   IT                           CC4               Value 4          Value 4 + Value 5

                                 CC5               Value 5

   OIL                         CC6               Value 6           Value 6

How do i get the total in th 'Total' column as above? Any idea how to sum it according to the department and display it in a single row and in the column 'Total'?

Here's the code that i construct until 'Balance' column. Im clueless on this part. Any help wud be great.

<asp:SqlDataSource ID="dsGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MyConn %>" ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>" 
        SelectCommand="SELECT d.dept_ID, d.dept_name, c.cc_ID, c.costCentre_name, IIF(ISNULL(i.Net, 0), 0, i.Net) - IIF(ISNULL(u.Net), 0, u.Net) AS Balance 
FROM ((((department d) 
INNER JOIN costCentre c ON d.dept_ID=c.dept_ID) 
LEFT JOIN (SELECT cc_ID, SUM(amount) AS Net FROM monthlyIncome GROUP BY cc_ID) i ON c.cc_ID=i.cc_ID) 
LEFT JOIN (SELECT cc_ID, SUM(amount) AS Net FROM utilization GROUP BY cc_ID) u ON c.cc_ID=u.cc_ID)" 
        FilterExpression="Convert(dept_ID, 'System.String')  like '{0}%'"> 
            <FilterParameters> 
            <asp:ControlParameter Name="dept_ID" ControlID="ddlDept" PropertyName="SelectedValue" /> 
            </FilterParameters> 
            </asp:SqlDataSource>

Thanks in advance!

Answer 1

User-830235260 posted

You can find the sum in query. Use sub query for calculating sum, group by DeptId

Answer 2

User3866881 posted

Hello：）

As far as I see，I think it not easy to deal with directly with LINQ to SQL……But I think you can use the sample of MSDN to group the GridView with the same cell's value and fetch the total result as Sum……：

http://www.agrinei.com/gridviewhelper/gridviewhelper_en.htm