getting a sum in columns from different tables in a sqlserver-ce database and displaying the result in a label control

I have a sqlserver compact database with five tables A, B, C, D and E. There is no relation defined between the tables. All the tables have an amount column where I need to get the sum from the first four tables and subtract the sum from table E (i.e calculate if I should get a profit or a loss). I have searched for problems pertaining the same to no avail.

What I expect to get: sum(A)+sum(B)+sum(C)+sum(D)-sum(E), which is to be defined as a command text passed to a sqlcecommand object. The result is to be displayed in a label control.

this is what I have so far:

SqlCeCommand sqlCommand = new SqlCeCommand();
            login = login.getLogin();
            SqlCeDataReader sqlDataReader;

            string cmdString =
                "SELE开发者_如何学运维CT SUM(AmountReceived) FROM A WHERE Date BETWEEN (@param1) AND (@param2);" +
                "SELECT SUM(AmountCharged) FROM B WHERE DateOfEntry BETWEEN (@param1) AND (@param2);" +
                "SELECT SUM(Amount) FROM C WHERE Date BETWEEN (@param1) AND (@param2);" +
                "SELECT SUM(AmountCharged) FROM D WHERE DateOfRequest BETWEEN (@param1) AND (@param2);" +                
                "SELECT SUM(AmountDue) FROM E WHERE Date BETWEEN (@param1) AND (@param2);";

            sqlCommand.Connection = login.connection;
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.CommandText = cmdString;
            sqlCommand.Parameters.Add("@param1", SqlDbType.DateTime).Value = dateTimePickerFromPandL.Text;
            sqlCommand.Parameters.Add("@param2", SqlDbType.DateTime).Value = dateTimePickerToPandL.Text;
            sqlDataReader = sqlCommand.ExecuteReader();

            if (sqlDataReader.Read())
            {

                labelProfitOrLossAmount.Text = "Ksh : " + //value expected to go here

            }

Where do I go from here?

---

SELECT SUM(amount) FROM (
  SELECT AmountReceived [Amount] FROM A WHERE Date BETWEEN (@param1) AND (@param2)
  UNION ALL
  SELECT AmountCharged FROM B WHERE DateOfEntry BETWEEN (@param1) AND (@param2)
  UNION ALL
  SELECT Amount FROM C WHERE Date BETWEEN (@param1) AND (@param2)
  UNION ALL
  SELECT AmountCharged FROM D WHERE DateOfRequest BETWEEN (@param1) AND (@param2)            
  UNION ALL
  SELECT -AmountDue FROM E WHERE Date BETWEEN (@param1) AND (@param2)
) [subquery]

If you put this between @" and " then you don't need all the string concatenation syntax.

---

You're better off using a single command in your query. I think you can handle multiple ones using SqlCommand, but I couldn't tell you how to do it. Here's a possible query:

SELECT sumA + sumB + sumC + sumD - sumE as total FROM
(
    SELECT (SUM(AmountReceived) FROM A WHERE Date BETWEEN (@param1) AND (@param2)) AS sumA,
    SELECT (SUM(AmountCharged) FROM B WHERE DateOfEntry BETWEEN (@param1) AND (@param2)) AS sumB,
    SELECT (SUM(Amount) FROM C WHERE Date BETWEEN (@param1) AND (@param2)) AS sumC,
    SELECT (SUM(AmountCharged) FROM D WHERE DateOfRequest BETWEEN (@param1) AND (@param2)) as SumD,                         
    SELECT (SUM(AmountDue) FROM E WHERE Date BETWEEN (@param1) AND (@param2)) AS SumE
) as tblSum

Then use "sqlCommand.ExecuteScalar().ToString();" to get the result.

Note that this is very inefficient SQL, but it should work.