How to use the SUM function in MySQL to calculate the sum of numeric columns in a data table

How to use the SUM function in MySQL to calculate the sum of numeric columns in a data table

In the MySQL database, the SUM() function is a very powerful aggregate function, which is used to calculate all the numbers in a column Sum. Whether it is summation calculations or statistical data, the SUM function is a very important tool. This article will introduce how to use the SUM function for sum calculation in MySQL and provide corresponding code examples.

1. Create a sample table

Before we begin, we need to create a sample data table for demonstration. Suppose we have a table named students, which contains the following fields:

CREATE TABLE students
(
  id INT PRIMARY KEY,
  name VARCHAR(50),
  score DECIMAL(5,2)
);

The table contains three fields, namely id (student ID), name (student name) and score (student score). Next, we insert some sample data into the students table:

INSERT INTO students (id, name, score) VALUES
(1, '张三', 80),
(2, '李四', 90),
(3, '王五', 85),
(4, '赵六', 95),
(5, '钱七', 70);

Now that we have created a sample data table, we can start using the SUM function to perform sum calculations.

2. Use the SUM function for sum calculation

To use the SUM function for sum calculation, we need to use the SELECT statement and use the SUM function to calculate the corresponding column. The following is a simple example:

SELECT SUM(score) AS total_score FROM students;

In the above example, we use the SUM function to sum the score column, and use the AS keyword to give the calculation result an alias total_score. After executing the above query, we will get the following results:

total_score
----------
420.00

The above results indicate that the sum of the score column in the students table is 420.00.

In addition to summing the entire column, we can also calculate the sum of partial data based on specific conditions. For example, we can use the WHERE clause to filter specific data rows, and then sum the filtered data. The following is an example of summation calculation based on conditions:

SELECT SUM(score) AS total_score FROM students WHERE score >= 80;

In the above example, we calculated the sum of the scores of students with scores greater than or equal to 80. After executing the above query, we will get the following results:

total_score
----------
350.00

The above results indicate that the total scores of students with scores greater than or equal to 80 in the students table are 350.00.

3. Summary

This article introduces the method of using the SUM function for sum calculation in MySQL, and gives detailed code examples. By using the SUM function, we can easily sum the numeric columns in the data table to obtain the required statistical results. In addition to simple sum calculations, we can also filter and calculate based on specific conditions to meet different needs.

In short, the SUM function is a very powerful and practical function in MySQL, which is very helpful for data statistics and sum calculations. I hope this article has been helpful to you in using the SUM function for sum calculations in MySQL.

The above is the detailed content of How to use the SUM function in MySQL to calculate the sum of numeric columns in a data table. For more information, please follow other related articles on the PHP Chinese website!