How to Compute the Sum of All Rows of a Column of a MySQL Table Using Python

In this article, we show how to compute the sum of all rows of a column of a MySQL table using Python.

This has application for a lot of things.

One thing is a shopping cart.

A user puts items in the shopping cart. These items get loaded to a MySQL table. There is a price column in this table, so all of the prices from all of these rows equals the subtotal of the purchase (before sales tax is applied, if applicable).

If you need to know how to install MySQL, see How to Install MySQL in Python 3.

So, the general code to find the sum of all rows of a column in a MySQL table is shown below.

cursor.execute("SELECT SUM(column_name) AS totalsum FROM Table_name") result = cursor.fetchall() for i in result: print(i[0])

So, the code above finds the sum of the column, column_name, of the table, Table_name.

If you save the total in a variable so that you can use it in other areas of the script, the code would be as that shown below.

cursor.execute("SELECT SUM(column_name) AS totalsum FROM Table_name") result = cursor.fetchall() for i in result: total= float(i[0])

Now, the sum of the column is in the variable total. You can then use it to do anything else, including getting the sales tax for the order, and multiplying the sum by the sales tax to get the final amount due for the customer.

So, this is how the code can be written.

We will now go through a full, complete example of finding the sum of all rows of a column.

So let's say we have a table called CustomerOrders and the table, shown below, represents all the items that the user has put in his or her shopping cart for purchase.

We want to write a Python script so that we find the sum of the price column. Thus, this will give us the subtotal for the customer's order.

So the code below is the full script to get the sum (the subtotal) of the price column of this CustomerOrders table.

import pymysql pymysql.install_as_MySQLdb() import MySQLdb db= MySQLdb.connect("hostname", "username", "password", "database_name") cursor= db.cursor() cursor.execute("SELECT SUM(price) AS totalsum FROM CustomerOrders") result = cursor.fetchall() for i in result: print(i[0]) db.close()

So, this is all the code that is needed to find the sum of all of the rows in a the table named Table_name.

So we first must import MySQLdb. Once that is imported, we gain connection to the MySQL database using the MySQLdb.connect() function. To see a full-length article on connecting to a MySQL database, see How to Connect to a MySQL Database in Python.

We then have to create a cursor for the table.

Next, we execute our function to find the sum of the price column of the CustomerOrders table using the cursor.execute() function. Inside of this function, we place in the line, "SELECT SUM(price) AS totalsum FROM CustomerOrders". So the whole line of code, is, cursor.execute("SELECT SUM(price) AS totalsum FROM CustomerOrders")

We then create a variable named result and set it equal to cursor.fetchall().

We then use a for loop and print out i[0], which represents the sum (subtotal) of the price column.

We then close the database once we've done what we've needed to.

Again, you can use the total variable and save the total in this variable. You can then print it out and do further mathematical operations with this sum such as multiply it by the sales tax to get the total amount due for the customer.

The program prints out 27.5, because this is the sum of the table shown above.

And this is we can find the sum of all of the rows of a column in a MySQL table using Python.

Related Resources

How to Show All Tables of a MySQL Database in Python

How to Count the Number of Rows in a MySQL Table in Python