Hi, I provide training in SAP UI5. Also feel free to checkout my new web page https://saptechblog.com
SAP HANA

Aggregate functions in SQL

 

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:

clip_image001

Result:

clip_image002

 

We can also rename the columns using the addition as:

Query:

clip_image003

Result:

clip_image004

 

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:

clip_image005

Result:

clip_image006

 

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:

 clip_image007

 

Result:

clip_image008

 

 

Max:

This function returns the maximum value of the column queried.

Query:

clip_image009

Result:

clip_image010

 

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:

clip_image011

Result:

clip_image012

 

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:

clip_image013

Result:

clip_image015

 

Hope you found this tutorial useful.

 

 

About Arun

0 Comments:

Post a Comment

Powered by Blogger.