CODEWITHSUNDEEP
c#sqlsql-serveraddition
Dejsa Cocan
Dejsa Cocan

Reputation: 1569

Adding values returned by SQL query to get the total?

I have a function that gets the Quantity and Weight of each item for a user's order from a SQL table based on the order id:

protected decimal getOrderWeight(int orderid)
{
    string sqlCmd = "SELECT Weight, Quantity FROM OrderItems WHERE OrderId = @OrderId";
    int qty = 0;
    decimal wgt = 0;
    decimal totalWeight = 0;
    using (SqlConnection cn = new SqlConnection(ConfigurationManager.ConnectionStrings["AbleCommerce"].ToString()))
    {
        cn.Open();
        SqlCommand cmd = new SqlCommand(sqlCmd, cn);
        cmd.Parameters.AddWithValue("@OrderId", orderid);
        using (IDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                qty = Convert.ToInt32(reader["Quantity"]);
                wgt = Convert.ToDecimal(reader["Weight"]);     
                totalWeight = qty * wgt;
            }
            cn.Close();
        }
    }
    return totalWeight;
}

In the table (which is called OrderItems), each item has a quantity and a weight. However, the weight featured in the table is NOT the result of weight * quantity (i.e. item ABC has a quantity of 6 for Order #12345, and each ABC item weighs 1.00 lbs.). The function I created works in that it multiplies each order item's quantity by the weight (i.e. The total weight for all 6 ABC items is 6.00 lbs)

However, I now need to take these total weight values and add them together to get the total weight for the whole order. Right now, I am returning all the weights in an order: 6.00, 5.00, 2.80, 146.76, 6.02, 2.25

But I am not sure how to add all these together? I was thinking of doing a Split() but then I would be converting decimals to strings only to convert them back to decimals to add them together once "split." This seems awfully complicated...is there a better way?

Upvotes: 1

Views: 570

Answers (2)

Igos
Igos

Reputation: 211

Shouldn't you sum total weight values like this: totalWeight += qty * wgt;

Because now your totalWeight will contain total weight value of last item in order only.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270463

Is this what you are looking for?

SELECT sum(Weight * Quantity) as TotalWeight
FROM OrderItems
WHERE OrderId = @OrderId;

If so, this is an example of an aggregation query, which is a basic SQL concept. You should do this type of work in the database, because the database is optimized to work with large volumes of data and extract such results from the tables.

Upvotes: 3

Related Questions