# How to use avg and sum in SQLAlchemy Query?

Last Updated : 22 Nov, 2021

In this article, we are going to see how to use avg and sum in SQLAlchemy query using Python.

## Installing SQLAlchemy

SQLAlchemy is available via the pip install package.

`pip install sqlalchemy`

However, if you are using flask you can make use of its own implementation of SQLAlchemy. It can be installed using –

`pip install flask-sqlalchemy`

### Example

Before we move ahead, we need to have a database and a table to work with. For this example, we are using the MySQL database and have created a students table. The table has 3 columns and 6 records as shown below.

In the table, we have a float column `percentage` on which we will perform our average and sum operations using SQLAlchemy.

### Approach:

1. First, we import the sqlalchemy library as db for simplicity. All the sqlalchemy objects, methods, etc will be imported using db prefix for better clarity.
2. We then create the engine which will serve as a connection to the database to perform all the database operations.
4. Use the metadata information to fetch the students table from the database.
5. We can now write an SQLAlchemy query to fetch the required records. We first extract the average value of the percentage column using SQLalchemy’s `func.avg()` function. Then we use the `func.sum()` function to get the sum of the values in the percentage column. Note that in both cases we have used the method `func.round(val, 2)` to round off the values to 2 decimal places.
6. Print the output. In the output we can view that we have both the sum and average values for the percentage field.

## Python

 `import` `sqlalchemy as db ` ` `  `# Define the Engine (Connection Object) ` `engine ``=` `db.create_engine( ` `    ``"mysql+pymysql://root:password@localhost/Geeks4Geeks"``) ` ` `  `# Create the Metadata Object ` `meta_data ``=` `db.MetaData(bind``=``engine) ` `db.MetaData.reflect(meta_data) ` ` `  `# Get the `students` table from the Metadata object ` `STUDENTS ``=` `meta_data.tables[``'students'``] ` ` `  `# SQLAlchemy Query to get AVG ` `query ``=` `db.select([db.func.``round``(db.func.avg(STUDENTS.c.percentage), ``2``)]) ` ` `  `# Fetch the records ` `avg_result ``=` `engine.execute(query).fetchall() ` ` `  `# SQLAlchemy Query to get SUM ` `query ``=` `db.select([db.func.``round``(db.func.``sum``(STUDENTS.c.percentage), ``2``)]) ` ` `  `# Fetch the records ` `sum_result ``=` `engine.execute(query).fetchall() ` ` `  `# View the records ` `print``(``"\nAverage: "``, avg_result[``0``]) ` `print``(``"\nSum: "``, sum_result[``0``]) `

Output: