Addition of two Columns in third Column.

Hi Guys,

I got stucked in my project and i need your help.

I have a data grid with data from sql 2005. I have got two amount columns - Amount1 & Amount2 with values from database. I want the addition of both in Total column (type: template) which I added in data grid view I also want the grand total of Total column in the bottom.


Pls help
0
shailesh_das
7/13/2008 12:19:31 PM
asp.net.presentation-controls 72751 articles. 3 followers. Follow

15 Replies
808 Views

Similar Articles

[PageSpeed] 5

 >I have got two amount columns - Amount1 & Amount2 with values from database
Why not change the select? Change

SELECT  Amount1, Amount2 FROM FRED

to

 SELECT  Amount1, Amount2, (Amount1 + Amount2) AS Amount3 FROM FRED

This gives you your third column

To get the total as the buttom add a dummy column
 SELECT  1 AS X, Amount1, Amount2, (Amount1 + Amount2) AS Amount3 FROM FRED
UNION
 SELECT 2 AS X, SUM(Amount1) AS Amount1, SUM(Amount2) AS Amount2 , SUM(Amount1 + Amount2) AS Amount3 FROM FRED
ORDER BY 1


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
7/13/2008 12:44:30 PM

Hi Dude, Use as shown below.

select AA.Amount1,AA.Amount2,Sum(AA.Amount1+AA.Amount2) as Total from AllAmounts AA group by AA.Amount1,AA.Amount2

Sample Output

Amount1

Amount2

Total

1

2

3

23

24

47


Varun Kumar Adire
0
avarunkumar27
7/13/2008 12:49:48 PM
Thanks for the solution i dint thought that it can be easily done via sql query itself. I will implement this. But how do i make a grand total in the bottom of the Total Column?
0
shailesh_das
7/13/2008 12:51:29 PM

 The part in bold gives the total and the order by column 1, puts it as the last row.

 SELECT  1 AS X, Amount1, Amount2, (Amount1 + Amount2) AS Amount3 FROM FRED
UNION
 SELECT 2 AS X, SUM(Amount1) AS Amount1, SUM(Amount2) AS Amount2 , SUM(Amount1 + Amount2) AS Amount3 FROM FRED
ORDER BY 1


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
7/13/2008 12:59:49 PM

use this 

select AA.Amount1,AA.Amount2,(AA.Amount1+AA.Amount2) as Total from AllAmounts AA group by AA.Amount1,AA.Amount2

union

select Sum(Amount1),Sum(Amount2),(Sum(Amount1)+Sum(Amount2)) from AllAmounts

-- select * from AllAmounts

Amount1  Amount2 Total
1 2 3
23 24 47
24 26 50

 

Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.

 


Varun Kumar Adire
0
avarunkumar27
7/13/2008 1:02:56 PM
ok i got it. But i need grand total of third column "Total" only and that too in Footer.
0
shailesh_das
7/13/2008 1:08:39 PM

 If you do not need totals of ammout 1 and amount 2 then

 SELECT  1 AS X, CONVERT(VARCHAR(20), Amount1) AS Amount1,
    CONVERT(VARCHAR(20),Amount2 AS AMOUNT2,
    (Amount1 + Amount2) AS Amount3 FROM FRED
UNION
 SELECT 2 AS X, '' AS Amount1, '' AS Amount2 , SUM(Amount1 + Amount2) AS Amount3 FROM FRED
ORDER BY 1
 


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
7/13/2008 1:54:09 PM

Here is a sample:

(Amount1, Amount2 are money data type in the table)

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        ShowFooter="true" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
                    SortExpression="id" />
                <asp:BoundField DataField="Amount1" HeaderText="Amount1" SortExpression="Amount1" DataFormatString="{0:c}" />
                <asp:BoundField DataField="Amount2" HeaderText="Amount2" FooterText="Total:" FooterStyle-HorizontalAlign="right" SortExpression="Amount2" DataFormatString="{0:c}" />
                <asp:TemplateField HeaderText="Amount1+Amount2" >
                  
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# AmountAdd(Eval("Amount1"),Eval("Amount2")).ToString("C2") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate> <%# AmountTotal().ToString("C2") %></FooterTemplate>
                  
                </asp:TemplateField>
            

            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:forumConnectionString %>"
            SelectCommand="SELECT [id], [Amount1], [Amount2] FROM [Amounts2]"></asp:SqlDataSource>
    

Helper functions:  

  decimal Total;
   
    public decimal AmountAdd(object Amt1, object Amt2)
    {
        decimal dAmt12;
        decimal dAmt1;
        decimal dAmt2;
        if (Amt1 != DBNull.Value)
        {
          dAmt1=Convert.ToDecimal(Amt1);
        }
        else
        {
             dAmt1=0;
        }
         if (Amt2 != DBNull.Value)
        {
          dAmt2=Convert.ToDecimal(Amt2);
        }
        else
        {
             dAmt2=0;
        }

        dAmt12 = dAmt1 + dAmt2;
   
        Total += dAmt12;
        return dAmt12;
    }
    public decimal AmountTotal()
    {
        return Total;
    }
 
Limno

0
limno
7/13/2008 9:39:30 PM
Hi Tatworth, actually my query is like this- "SelectCommand="SELECT [ID], [LicenseNo], [Agent_Name], CONVERT(nvarchar(30), Regd_Date,103) AS Regd_Date,[Type], [Bank_Name], [DD_No1], CONVERT(nvarchar(30), DD_Date1,103) AS DD_Date1, [DD_Amt1], [DD_No2], CONVERT(nvarchar(30), DD_Date2,103) AS DD_Date2, [DD_Amt2],(DD_Amt1+DD_Amt2) AS Total, [MR_No], CONVERT(nvarchar(30), MR_Date,103) AS MR_Date FROM [Agency]
WHERE [Regd_Date] >= @StartDate AND [Regd_Date] <= @EndDate">

so union method may not work here but i implemented the addition of two amt columns "(DD_Amt1+DD_Amt2) AS Total".

@limno, thanks for helping me out. Your solution looks good i am going to test it. I am using small int for the amount so i have to make some modification. I will post back when i test it.
0
shailesh_das
7/14/2008 6:46:21 AM

 The union should work provided the same WHERE clause is in both parts.


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
7/14/2008 7:00:22 AM
@limno- I am a newbie and i dont know how to port the code to VB.Net. Can u please give the above code in VB.NET.

thanks
0
shailesh_das
7/15/2008 6:34:40 AM

 Try using the TSQl I gave you, it will save you having to change your vb.net form!


Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped.
0
TATWORTH
7/15/2008 8:07:25 AM

 

 

 

 <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
        ShowFooter="true" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True"
                    SortExpression="id" />
                <asp:BoundField DataField="IntAmount1" HeaderText="Amount1" SortExpression="IntAmount1" DataFormatString="{0:N1}" />
                <asp:BoundField DataField="IntAmount2" HeaderText="Amount2" FooterText="Total:" FooterStyle-HorizontalAlign="right" SortExpression="IntAmount2" DataFormatString="{0:c}" />
                <asp:TemplateField HeaderText="Amount1+Amount2" >
                  
                    <ItemTemplate>
                        <asp:Label ID="Label1" runat="server" Text='<%# AmountAdd(Eval("IntAmount1"),Eval("IntAmount2")).ToString("N1") %>'></asp:Label>
                    </ItemTemplate>
                    <FooterTemplate> <%# AmountTotal().ToString("C2") %></FooterTemplate>
                  
                </asp:TemplateField>
            

            </Columns>
        </asp:GridView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:test_for_forumConnectionString %>"
            SelectCommand="SELECT [id], [IntAmount1], [IntAmount2] FROM [Amounts]"></asp:SqlDataSource>
    
 
 Private Total As Decimal

    Public Function AmountAdd(ByVal Amt1 As Object, ByVal Amt2 As Object) As Decimal
        Dim dAmt12 As Decimal
        Dim dAmt1 As Decimal
        Dim dAmt2 As Decimal
        If Amt1 IsNot DBNull.Value Then
            dAmt1 = Convert.ToDecimal(Amt1)
        Else
            dAmt1 = 0
        End If
        If Amt2 IsNot DBNull.Value Then
            dAmt2 = Convert.ToDecimal(Amt2)
        Else
            dAmt2 = 0
        End If

        dAmt12 = dAmt1 + dAmt2

        Total += dAmt12
        Return dAmt12
    End Function
    Public Function AmountTotal() As Decimal
        Return Total
    End Function
 
Limno

0
limno
7/15/2008 2:33:43 PM
Thanks a lot limno. 
Your code worked like a charm. I did some modifications as i wasnt using currency datatype i changed it to integer and it worked great!

Thanks!
0
shailesh_das
7/16/2008 2:48:32 PM
Ok I am stuck again. But this time with a single smallint column and varchar column.

I have two colums 

Title (varchar), Amount(smallint)

i want the total of Amount at the bottom.
0
shailesh_das
7/17/2008 7:03:56 AM
Reply:

Similar Artilces:

Compare Two columns and update the Third Column
 Hi,I have three columns and want to update the third column based on the comparison of first two columns in the same table.For instance:Col1 - val1, val2, val3, val4, val5......Col2 - NULL, NULL, val1, NULL, val2....Col3 - TRUE, TRUE, FALSE, FALSE, FALSE....Thanks for any help. When you define a column, you can define it as a "computed column". Make col3 a computed column and fill in the comparison expression.    Dear David,Thanks for your reply. Actually I have table with more then 7000 records in it. and all the columns are already defined. I was lookin...

display the sum of two datagrid columns into third column
hi... there..     i have a datagrid with columns named Price ,Quantity and total. now in the total column i  wanna display the prices which comes by multiplyiing the price and quantity (like  total=Price * quantity). I'm workin in asp.net +vb.net.                     may i knw hw can i do this.. plz write the code.. thanks.         Hi, do like this..suppose u have,        string ProductPrice = ...

Multiply two columns and store the result into third column
Hi,  I am new to asp.net world. I am having 1 problem in a gridview.   There are 3 template fields in my gridview namely Length , Width and Area. Length n Width contain textboxes and Area is having label. Problem is as soon as you enter the values in textboxes, the area should be calculated and reflected immediately in the area field of that row. I am using RowDataBound event gridview. But area is not getting update dynamically after changing the textfields.  Code i m using is: protected void gvArea_RowDataBound(object sender, GridViewRowEventArgs ...

When is a column not a column?
I have several extremely similar datawindows that I have to maintain that go against the same table. Recently, I had to add a column to that table, and also add the column to ALL of these datawindows that go against this table. I added the column to the first datawindow, set up the edit control style, etc. I then did a copy/paste of the columns edit control to the remaining dw's (of course remembering to first add the column in SQL). Everything (update props, column specs, column props) seems to indicate that the pasted column is linked to the column I selected, HOWEVER, when I...

How to subtract two date columns(bound) of a Datagrid and display the result in the third column?
Hi Everyone,I  have two date columns(databound-coming from SQL database) in my datagrid, 'start date' & 'end date'. I need to calculate number of days with 'end date- start date' and display the result in the third datagrid column. i.e.: column2-column1 = column3Any help anyone?...ASAP please got a deadline to meet :) (SOS!!!)Many Thanks in advance,Arty.Aartee...we live more than once! Hi, there are several ways of doing it. One of them is creating a label in the TemplateColumn, use the Helper function to calculate the number of days.<ItemTemplate>   <...

How to subtract two numeric columns(bound) of a Datagrid and display the result in the third column?
Hi Everyone,I  have two numeric columns(databound-coming from SQL database) in my datagrid, 'PromoPrice' & 'CostPrice'. I need to calculate 'PromoPrice- CostPrice' and display the result in the third datagrid column. i.e.: column2-column1 = column3Any help anyone...Many Thanks in advance,Nico. In your query do this:Select .... , (PromoPrice - CostPrice) AS Answer, .....FROM yourTableKeep in mind you can do math functions in queries.DarmarkMark as Answer, if this reply answers your post. Hi Darmark, thanks for your fast answer.The fact is I'm using a single "select function"...

how to split the content of one column in the objectdatasource into two parts and let them display in two columns in the GridView?
i combine the title and the body of one article and store it in one column in the database,but i want to show the title and body seperately in the GridView,how should i achieve this goal?thank you so much for your advice!!! you can write the logic of splitting the column into two (title and body) in the query itself by which you are filling the datasource..Thanx, [KaushaL] || BloG || Profile || Microsoft MVP"I would love to change the world, but they won’t give me the source code"Don't forget to click "Mark as Answer" on the post that helped you.This credits that me...

Combine Two Different Columns into one column
Hi Guys,   I have twotables(Employee and Borrower). In Employee table have EMPID and Borrower table have BorrowerID. I want to comebine these two columns into one column as EMPID  in Employee table. Can  any one help? Thanks what is realtion between to tables ?   Use    Union Thanks for reply. Acutally Employee table EMPID, NAME Columns. in this Employee table had 12000 rows. Borrower table has BorrowerID,BType etc.. .Here Employee ID and BorrowerID both are same. This table has 16000 rows. Now my what i want to do is Come EMPID and BorrowerID and ...

Grouping two columns into one column with GridView
I would like to modify the default output of a gridview to group two columns into one column. This is probably best explained by example. So, lets say there is a simple gridview with the output something like this:Name           Description                           Date------------------------------------------------------------------Foo            Some description of foo     &nb...

3 column design insetad of two column
I would like to have 3 column design for csk, similar to www.buysellcommunity.com. How can I achieve this? What changes do I need to make to the CSS? Does anybody has sample code?Rafal Zikbay.com - Free Classifieds (Search Over 5 mln jobs on Zikbay.com) DNN Direct I don't know what you are using currently, but this article describes how to design a website with Pure CSS (and the last example is a 3 colum design): http://www.singingeels.com/Articles/How_To_Pure_CSS_Design.aspx-Timothy Khourihttp://www.SingingEels.com/Developer / Architect / Author There are some really good CSS templ...

Is it possible to limit the items in a dataList control to two columns and two rows?
or with sql?   to have less columns select less field to have less row use the top function. I really suggest you do not use * to get all fields its longer and normally you do not need all tables fieldDo not forget to mark as answer all message who help you.Don't forget to check if you can answer some of our question to. sorted thanks...

two column repeater control?
I want to display my data in a two column repeater control.  Art some point it may go to three columns.  My control looks like this:          <asp:Repeater ID="Repeater1" runat="server">            <HeaderTemplate>              <table class="p_innerTable">            </HeaderTemplate>     ...

two db table column, one datagrid column
Dear all, I have a db table which have two column, first name and last name. Can I show them in one datagrid column? Thanks Yes you may. Through the usage of the TemplateColumn, yes. An example of this: <code> <asp:DataGrid runat="server" ID="dgSomeGrid">     <Columns>         <asp:TemplateColumn>             <ItemTemplate>                 <%# DataBinder.Eval(Container.DataSource, "LastName") %&g...

column header to span two columns in a datagrid display
Is it possible for a column header to span two columns? If so how would this be done? Thanks In the ItemDataBound (or ItemCreated) check if Item is of type Header and if so - define the colspan property of the Cell object which you want to define the Colspan for and delete the next Cell from hte DataGridItem CheersBug [MCSD] Is it possible to span header column in design view only. using some kind of CSS. I'm using ASP,NET 3.5 and GridView...

Web resources about - Addition of two Columns in third Column. - asp.net.presentation-controls

Addition - Wikipedia, the free encyclopedia
Addition (often signified by the plus symbol "+") is one of the four elementary , mathematical operations of arithmetic ; with the others being ...

Facebook Tests Addition of Trending Module to Search Screen in iOS App
Facebook is testing the addition of a trending module on the search screen of its flagship iOS application, the social network confirmed to sister ...

In addition to the... - Facebook
Magpul Industries Corp. hat geschrieben: In addition to the national battle to... Tritt Facebook bei, um dich mit Magpul Industries Corp. und ...

Facebook updates payments terms to reflect addition of subscriptions, Credits phase out and more
Facebook has updated its payment terms for both users and developers following Tuesday’s announcement that it would support monthly subscription ...

Scripting News: Should the Community Feed be an RSS feed in addition to being an OPML feed?
This question came up in the Community Feed, which you can read in Fargo, by choosing the Community Feed command from the Docs menu . Or you ...

Meet the new additions to the Stack Exchange clan
The fun continues — in the past month and a half we’ve welcomed seven new hires! We’re growing at a steady pace and we don’t plan to stop. Get ...

Math Fight Pro: School Edition - 2 Player Reactor Math Game for Kids - Addition, Subtraction, Multiplication ...
Get Math Fight Pro: School Edition - 2 Player Reactor Math Game for Kids - Addition, Subtraction, Multiplication & Division! on the App Store. ...

Six new additions to Open House Perth you need to get excited about
The newest locations in Perth which have never before been open to the public &ndash; until now.

Apple TV’s App Store gets better curation with addition of categories
... Apple TV Continue reading more about Apple TV at 9to5Mac . What do you think? Discuss "Apple TV’s App Store gets better curation with addition ...

US oil rig additions seen providing temporary cash boost
Drillers put rigs back to work in US oil fields after more than two months of pulling back as explorers seek to boost production to help pay ...

Resources last updated: 11/26/2015 8:01:44 PM