Total sum from two tables

In this blog, we will learn how to find total sum from two tables and show the output in a Gridview.


Suppose there are two tables named as t1 and t2 respectively. In table t1 there are three columns as id, date, amount and in table t2 contains id, name, address. We need to display id, name, address and total amount from two tables.

 

The scenario is

 

T1 table data

 

id                date               amount

1             10/05/2000            100

2             10/05/2001             200

3             12/05/2000             300

1             10/05/2000             232     

2             14/05/2000             100

 

T2 table data

 

id                name               address

1                   Raj                  Pune

2                   Ravi                 Mumbai

3                   Rahul               Delhi

 

 

I need the output as below

 

id                name               address           Total

1                   Raj                  Pune                332

2                   Ravi                 Mumbai          300

3                   Rahul               Delhi               300

 

 

 

 

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>

 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

 

<html xmlns="http://www.w3.org/1999/xhtml">

<head runat="server">

    <title>Untitled Page</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

   

    </div>

    <asp:GridView ID="GridView1" runat="server">

    </asp:GridView>

    </form>

</body>

</html>

 

using System;

using System.Configuration;

using System.Data;

using System.Linq;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.HtmlControls;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Xml.Linq;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page

{

    string strConnString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

    string str = null;

    SqlCommand com;

    protected void Page_Load(object sender, EventArgs e)

    {

        SqlConnection con = new SqlConnection(strConnString);

        con.Open();

        str = "select t1.id, t2.name, t2.address, sum(t1.amount)from t2 inner join t1 on t2.id=t1.id group by t1.id, t2.name, t2.address";

        com = new SqlCommand(str, con);

        SqlDataReader reader = com.ExecuteReader();

        GridView1.DataSource = reader;

        GridView1.DataBind();

        con.Close();  

       

    }

}

 

 

Thanks for reading.

Ebook Download
View all
Learn
View all