This tutorial explains the Basic aggregate functions supported in the SQL.
The aggregate functions are as below:
Function | Description |
Count | This function returns the count of no of row selected by the query. E.g if you want to query no of entries in the database table you can use count to return he total no of entries. |
Min | This function returns the least value of the column specified. |
Max | This function returns the maximum value of the column specified |
Sum | This function returns the total value of the columns specified. Count specifies the no of rows ,whereas sum adds the values and display the total |
Avg | This function returns the average value of the data selected |
For this tutorial I am using the table “Products” from schema “SAP_HANA_DEMO”
Count:
This function returns the count of no of row selected by the query. E.g if you wanted to query the no of rows in the table.
Query:
Result:
We can also rename the columns using the addition as:
Query:
Result:
Note: Provide the addition top 10 rows to fetch the first 10 records of the table, you get the count as 10. Count only calculates the no of rows returned from the database table using query, not all the records.
Min
This function is used to return the row containing the lease value for the specified column.
E.g. I wanted to know what is the least unit of measure in the table.
Query:
Result:
Group by:
This aggregate function was executed only for the column Weight measure, now in the result I also wanted to display a field on which aggregate function is not done. Say for example, I wanted to know the least unit of measurement category wise. Group by addition can be used in that case to group the aggregate function
Query:
Result:
Max:
This function returns the maximum value of the column queried.
Query:
Result:
SUM:
This function returns the total of the dataset selected. For example, if I wanted to know the total unit of measurement and Price category wise.
Query:
Result:
Note: Suppose if we use the function sum without grouping by category then the system returns the result of sum of all the rows. Here the sum is calculated category wise.
AVG:
This function is used to return the average of selected dataset. E.g If I wanted to query what is the average weight and price per category query can be done as below.
Query:
Result:
Hope you found this tutorial useful.
0 Comments:
Post a Comment