How to use SQL SELECT COUNT SUM AVG Function
SQL SELECT COUNT SUM AVG are aggregate Functions so, today’s you will learn SQL Aggregates function that you will calculate aggregates (SUM, AVG, COUNT).
and you will also learn some intermediate SQL clauses such as (ORDER BY, DISTINCT, GROUP BY), these efficiently use SQL for data analysis.
In the last tutorial, I have discussed the (MIN, MAX) to finding the minimums and maximums in a data set. which is also used for data analysis.
- The
SELECT COUNT() functionsreturn a count of the number of data values that match specified criteria. - The
SELECT SUM() functionreturns the total sum of the numeric column. - The
SELECT AVG() functionsreturn the average value of a numeric column.
The SQL SELECT COUNT SUM AVG Function Syntax
COUNT() Syntax
|
1 2 3 |
SELECT COUNT(column-name) FROM table-name WHERE condition; |
SUM() Syntax
|
1 2 3 |
SELECT SUM(column-name) FROM table-name WHERE condition; |
AVG() Syntax
|
1 2 3 |
SELECT AVG(column-name) FROM table-name WHERE condition; |
COUNT Function
The COUNT Function, its works on both numeric and non-numeric data types, these function returns the total numbers of values in the specified field.
Note: By default, all Aggregate functions before working on the data exclude nulls values.
Therefore, COUNT (*) function is a special implementation that returns the count of all the rows in a specific table.
Note: COUNT (*) also considers duplicates and nulls.
The given below table shows data in a customer_details table.
| Refer_number | Return_date | associate_number | purchase_id | purchase Amount |
|---|---|---|---|---|
| 21 | 23-11-2018 | 2 | 5 | 565.00 |
| 22 | 12-02-2018 | 3 | 3 | 1200.00 |
| 23 | Null | 4 | 4 | 600.00 |
| 24 | 12-02-2018 | 3 | 3 | 900.00 |
| 25 | 15-02-2018 | 5 | 3 | 777.00 |
In the given above table, we want to get the number of times that the purchase with id 3 has been goods purchase.
|
1 2 3 |
SELECT COUNT(`purchase_id`) FROM `customer_details` WHERE `purchase_id` = 3; |
Answer: 3
SUM function
The SUM function used for returns the SUM of all the values in the specified column and NULL values are also excluded from the result returned.
Note:- SUM function works on the only numeric fields only
Let us see the example, suppose that a shop owner wants a total amount collection report. the data shows in the below-given payment_details table.
| Customer_id | Customer_name | Product | Purchase_Date | Amount |
|---|---|---|---|---|
| 11 | Jacky | Laptop | 17-07-2018 | 32000 |
| 12 | Charry | Desktop | 18-07-2018 | 22000 |
| 13 | Sumit Negi | Iphone | 20-07-2018 | 52000 |
| 14 | Dyanna vist | Laptop | 26-07-2018 | 42000 |
| 15 | Breden Mark | Iphone | 29-07-2018 | 72000 |
We want to get the total amount collected as in the given table, therefore sums them up to return a single result.
|
1 2 |
SELECT SUM(`amount`) FROM `payment_details`; |
Answer: 220000
AVG function
AVG Function always returns the average values in a specified column, we can say that just like the SUM function.
Note: AVG function works only on numeric data types.
Let’s see the example of AVG function, which gives us average values in a specified column, for example, we considered the above payment_details table, the table shows numeric data in the table columns.
|
1 2 |
SELECT AVG(`Amount`) FROM `payment_details`; |
Answer: 44000
Conclusion:-
- the SUM and AVG functions only work on numeric data types.
- The COUNT, SUM, AVG, MIN, and MAX are the aggregate functions.
- suppose that, If you want to exclude duplicate values from the aggregate function results, then use the DISTINCT keyword.
- Aggregate functions (SUM, AVG, MIN, and MAX ) can be used in conjunction with other SQL clauses such as GROUP BY
- Intermediate SQL clauses (GROUP BY, ORDER BY, DISTINCT) that you have to know to efficiently use SQL for data analysis!
About Author
