SUM and UNION for sum two field from table
hi guys
i used this cod for sum two field from table :
--------------------------------------------------
private void button5_Click(object sender, EventArgs e)
{
int a = 0;
try
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Shopping;Integrated Security=True");
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandText = "SELECT TOP 1 Mojodi FROM TMojojdi ORDER BY Id DESC";
con.Open();
SqlDataReader dr = com.ExecuteReader();
if (dr.Read())
{
a = Int32.Parse(dr[0].ToString());
}
con.Close();
}
catch
{
}
finally
{
con.Close();
con.Dispose();
}
try
{
SqlConnection con = new SqlConnection("Data Source=.;Initial Catalog=Shopping;Integrated Security=True");
SqlCommand com = new SqlCommand();
com.Connection = con;
com.CommandType = CommandType.Text;
com.CommandText = "Insert Into TMojojdi (Id,Mojodi,Ad,Kam) Values (@i,@m,@a,@k)";
com.Parameters.AddWithValue("@i", textBox6.Text);
com.Parameters.AddWithValue("@m", a = a - int.Parse(textBox7.Text));
com.Parameters.AddWithValue("@a", 0);
com.Parameters.AddWithValue("@k", textBox7.Text);
con.Open();
com.ExecuteNonQuery();
con.Close();
con.Dispose();
MessageBox.Show("Bardasht As Mojodi Ok", "Bardasht");
ShowGrid2();
}
catch
{
}
finally
{
con.Close();
con.Dispose();
}
}
-----------------------------------------------------------------------------------
result --> Mojodi = Mojodi + Ad ... then insert into next ROW from table !
how to change to query --> (SUM and UNION) ...
like this :
-----------------------------------------------------------------------------------
DECLARE @TBL TABLE (ID INT, AddValue INT)
INSERT INTO @TBL
SELECT 1 ID, 255 AddValue
UNION
SELECT 2, 45
UNION
SELECT 3, 200
SELECT T1.ID,
ISNULL((SELECT SUM(T2.AddValue)
FROM @TBL T2
WHERE T2.ID < T1.ID),0) AS [Sum]
,T1.AddValue
FROM @TBL AS T1
UNION
SELECT MAX(ID)+1,SUM(AddValue),NULL FROM @TBL