locked
Displaying sum from other table. RRS feed

  • Question

  • User1414062701 posted

    Guys,

    I have a table called Income where i will store costCentre and their income per Month. In this case, one costCentre can have few income amounts in that one table.

    So im trying to form a gridview that will tell the total income for a cost centre from that table, where it have to calculate all the amounts related to that particular costCentre and sum it up. The gridview will look like this:

    Department       |     Cost Centre     | Balance Income

         HR                            1                        23.00

                                         2                        26.00

                                         3                        45.00

          IT                            4                        34.00

                                         5                        22.00

    *the balance income is the sum of incomes from table income that related to each costCentre.

    Right now, im unsure on the way to calculate the total that is associated to the costCentre. Below is the code that i have tried but im getting this error : "You tried to execute a query that does not include the specified expression 'dept_ID' as part of an aggregate function."

    <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" AllowPaging="true" ShowFooter="true" AllowSorting="true" DataSourceID="dsGridView" PageSize="10" OnRowDataBound="GridView1_RowDataBound">
        <Columns>
        <asp:BoundField DataField="dept_name" HeaderText="Department Name" SortExpression="dept_ID" />
        <asp:BoundField DataField="costCentre_name" HeaderText="Cost Centre" SortExpression="cc_ID" />
        <asp:BoundField DataField="balance" HeaderText="Balance" />
        </Columns>
        </asp:GridView>
            <asp:SqlDataSource ID="dsGridView" runat="server" ConnectionString="<%$ ConnectionStrings:MyConn %>" ProviderName="<%$ ConnectionStrings:MyConn.ProviderName %>" 
            SelectCommand="SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name], (SUM(m.amount)) AS [balance]
            FROM department d, costCentre c, monthlyIncome m, utilization u 
            WHERE d.[dept_ID]=c.[dept_ID] AND c.[cc_ID]=m.[cc_ID] AND 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>

    Can anyone assist me on the way to get the related value to be displayed on the correct cell in the gridview?

    Thanks in advance!

    Friday, April 27, 2012 3:50 AM

All replies

  • User1376905052 posted

    Hello,

    Can you please provide your table structure that you are using to build the query.

    Friday, April 27, 2012 4:26 AM
  • User3866881 posted

    SelectCommand="SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name], (SUM(m.amount)) AS [balance]
            FROM department d, costCentre c, monthlyIncome m, utilization u
            WHERE d.[dept_ID]=c.[dept_ID] AND c.[cc_ID]=m.[cc_ID] AND c.[cc_ID]=u.[cc_ID]"
            FilterExpression="Convert(dept_ID, 'System.String') like '{0}%'">

    Hello:)

    I noticed it that you'd renamed the table "department" as another name called "d",So maybe Convert cannot recognize it……you can try to remove the alias name and do the following thing:

    SELECT DISTINCT d.[dept_ID], d.[dept_name], c.[cc_ID], c.[costCentre_name], (SUM(m.amount)) AS [balance]
            FROM department  costCentre c, monthlyIncome m, utilization u 
            WHERE department.[dept_ID]=c.[dept_ID] AND c.[cc_ID]=m.[cc_ID] AND c.[cc_ID]=u.[cc_ID]"
            FilterExpression="Convert(dept_ID, 'System.String') like '{0}%'">

    Saturday, April 28, 2012 8:48 PM
  • User1414062701 posted

    I have 4 tables:

    *department(dept_ID, dept_short_name)

    *costCentre(cc_ID, dept_ID, costCentre_name)

    *monthlyIncome(income_ID, cc_ID, month, amount)

    *utilization(util_ID, cc_ID, month, amount)

    One department can have many cost centres. So these cost centre will have their income every month and it will be stored in monthlyIncome table by month. So basically monthlyIncome can have many incomes for just one particular cost centre. Meanwhile, utilization table works in same way but value will be stored when income is used.

    So what im trying to do is actually to display the balance available for a particular cost centre to the user. So what it have to do is to calculate the sum in monthlyIncome and minus the sum in utilization according to costCentre and display it in dedicated row as per the related costCentre.

    Example:

    Department       |     Cost Centre     | Balance Income

         HR                            1                        23.00<----Value here is the sum(monthlyIncome) - sum(utilization), dedicated value for row

                                         2                        26.00

                                         3                        45.00

          IT                            4                        34.00

                                         5                        22.00

    Hope im clear here, any idea how to make it possible?

    Sunday, April 29, 2012 9:44 PM
  • User-1488931086 posted

    Hi——

    You should show all the records first,and then combine them together to show one for duplicated values……

    For more please refer this—— Groupped GridView:http://www.agrinei.com/gridviewhelper/gridviewhelper_en.htm

    Monday, April 30, 2012 2:54 AM