We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Calculating Total in a Query

linkcube1
linkcube1 asked
on
Medium Priority
199 Views
Last Modified: 2012-05-11
I'm trying to return the total cost of all the items returned from this query that puts items into a listView. I know using the SUM keyword will return the sum but int this case it is not working and I dont know why. CashOutTotal is the decimal value that I want to return the total. Any help would be appreciated. Thanks
private void frmMemberCashOutList_Load(object sender, EventArgs e)
        {
            SqlCeConnection con = BookStoreDB.GetConnection();
           // SqlCeCommand comm = new SqlCeCommand("SELECT BookID, MemberID, Title, Author, Price, Sum (Price) AS MemberPrice FROM [CashOutDB] Where MemberID = '" + ID + "'", con);
             SqlCeCommand comm = new SqlCeCommand("SELECT BookID, MemberID, Title, Author, Price, Sum(Price) AS NewPrice FROM [CashOutDB] Where MemberID = '" + ID + "'", con);
            con.Open();
            SqlCeDataReader reader = comm.ExecuteReader();
            while (reader.Read())
            {
                string[] data = new string[6];
                data[0] = reader["BookID"].ToString();
                data[1] = reader["MemberID"].ToString();         
                data[2] = reader["Title"].ToString();
                data[3] = reader["Author"].ToString();
                data[4] = reader["Price"].ToString();
                CashOutTotal = (decimal)reader["NewPrice"];
                ListViewItem liv = new ListViewItem(data);
                listView1.Items.Add(liv);



            }
            txtTotal.Text = CashOutTotal.ToString();
            con.Close();
        }

Open in new window

Comment
Watch Question

Full Stack .NET Developer
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Carlos VillegasFull Stack .NET Developer

Commented:
I think that you must remove the Sum(Total) as NewPrice from this line:
SqlCeCommand comm = new SqlCeCommand("SELECT BookID, MemberID, Title, Author, Price, Sum(Price) AS NewPrice FROM [CashOutDB] Where MemberID = '" + ID + "'", con);

Open in new window

And use the Price column instead...
I guess yv989c's solution will work in your case, but you could also check if the column you are trying to SUM, is correctly typed in the database, because if it's for example a varchar field, SUM won't work. Currency fields use to have double type or something like that.
Hope it helps.
Boccio
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.