Displaying total balance by department.

Question

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 the part to get the total by department. 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

You need to distinguish between data retrieval and reporting. To retrieve the data you can use the ROLLUP operator:

DECLARE @Sample TABLE
    (
      Department VARCHAR(3) ,
      CostCentre CHAR(3) ,
      Balance INT
    );

INSERT  INTO @Sample
VALUES  ( 'HR', 'CC1', 1 ),
        ( 'HR', 'CC2', 2 ),
        ( 'HR', 'CC3', 3 ),
        ( 'IT', 'CC4', 4 ),
        ( 'IT', 'CC5', 5 ),
        ( 'OIL', 'CC6', 6 );    

SELECT  Department ,
        CostCentre ,
        SUM(Balance) AS SumBalance
FROM    @Sample
GROUP BY Department ,
        CostCentre
        WITH ROLLUP;

The rest processing should be done by your ASP.NET application.