Summarizing Data for Columns in SQL
In this article, we are going to learn how to get the SQL summary of data in each column using MAX, MIN, AVG, COUNT, and SUM functions. Examples are supported for both SQL Server & MySQL.
- MAX/MIN function returns the maximum/minimum of a column.
- AVG function returns the column average.
- COUNT function returns the number of rows of a column.
- SUM function returns the sum of all the rows of a column.
Consider the Students table for our examples. Marks taken by each student for each subject are stored in the table.
Students Table:
Id | Science | Maths | Commerce | Languages |
1 | 70 | 80 | 60 | 90 |
2 | 90 | 100 | 70 | 90 |
3 | 80 | 80 | 80 | 80 |
4 | NULL | NULL | NULL | NULL |
5 | 60 | 70 | 80 | 60 |
6 | 90 | 90 | 100 | 90 |
7 | 40 | 30 | NULL | 50 |
8 | NULL | 60 | 50 | 40 |
9 | 70 | 60 | 60 | 70 |
10 | 95 | 80 | 70 | 60 |
Table 1. Sample Data for Students
Run the below script to insert sample data.
Sample Data Script:
CREATE TABLE Students( Id INT NOT NULL, Science INT NULL, Maths INT NULL, Commerce INT NULL, Languages INT NULL, PRIMARY KEY (Id) ); INSERT Students(Id, Science, Maths, Commerce, Languages) VALUES (1, 70, 80, 60, 90); INSERT Students(Id, Science, Maths, Commerce, Languages) VALUES (2, 90, 100, 70, 90); INSERT Students(Id, Science, Maths, Commerce, Languages) VALUES (3, 80, 80, 80, 80); INSERT Students(Id, Science, Maths, Commerce, Languages) VALUES (4, NULL, NULL, NULL, NULL); INSERT Students(Id, Science, Maths, Commerce, Languages) VALUES (5, 60, 70, 80, 60); INSERT Students(Id, Science, Maths, Commerce, Languages) VALUES (6, 90, 90, 100, 90); INSERT Students(Id, Science, Maths, Commerce, Languages) VALUES (7, 40, 30, NULL, 50); INSERT Students(Id, Science, Maths, Commerce, Languages) VALUES (8, NULL, 60, 50, 40); INSERT Students(Id, Science, Maths, Commerce, Languages) VALUES (9, 70, 60, 60, 70); INSERT Students(Id, Science, Maths, Commerce, Languages) VALUES (10, 95, 80, 70, 60); |
Script 1. Sample Data Script
Summarize Data for a Column.
Using MAX, MIN, AVG, COUNT and SUM functions we can create a query to get the summary of a column.
Example 1. SQL Summary of Marks for Science
Run the below SQL summary query to get the summary of marks for Science.
SQL Script:
SELECT 'Science' AS Subject, MAX(Science) AS Maximum, MIN(Science) AS Minimum, ROUND(AVG(Science), 0) AS Average, COUNT(Science) AS Students, SUM(Science) AS Sum FROM Students |
Script 2. Science Summary
Summarized data for Science are shown in the below table.
Subject | Maximum | Minimum | Average | Students | Total |
Science | 95 | 40 | 74 | 8 | 595 |
Table 2. Science Summary
Summarize Data for each Column in SQL
Using the UNION command we can create a query to get the SQL summary of each column.
Example 2. Summarize Data for Subjects
Runt the below SQL summary query to get the summary of marks for each subject.
SQL Script:
SELECT 'Science' AS Subject, MAX(Science) AS Maximum, MIN(Science) AS Minimum, ROUND(AVG(Science), 0) AS Average, COUNT(Science) AS Students, SUM(Science) AS Sum FROM Students UNION SELECT 'Maths' AS Subject, MAX(Maths) AS Maximum, MIN(Maths) AS Minimum, ROUND(AVG(Maths), 0) AS Average, COUNT(Maths) AS Students, SUM(Maths) AS Sum FROM Students UNION SELECT 'Commerce' AS Subject, MAX(Commerce) AS Maximum, MIN(Commerce) AS Minimum, ROUND(AVG(Commerce), 0) AS Average, COUNT(Commerce) AS Students, SUM(Commerce) AS Sum FROM Students UNION SELECT 'Languages' AS Subject, MAX(Languages) AS Maximum, MIN(Languages) AS Minimum, ROUND(AVG(Languages), 0) AS Average, COUNT(Languages) AS Students, SUM(Languages) AS Total FROM Students ORDER BY Subject; |
Script 3. Subject Summary
The below table shows the result for the SQL summary query of marks for each subject.
Subject | Maximum | Minimum | Average | Students | Total |
Commerce | 100 | 50 | 71 | 8 | 570 |
Languages | 90 | 40 | 70 | 9 | 630 |
Maths | 100 | 30 | 72 | 9 | 650 |
Science | 95 | 40 | 74 | 8 | 595 |
Table 3. Subject Summary
