MIN, MAX, AVG, SUM, COUNT

[Former Member]

Hi Everybody

can anybody explain me the use of all MIN, MAX, AVG, SUM, COUNT while accessing database records

how and where we use these functions??

Thanks in advanc

[former_member181962]

These syntaxes are usually called as aggregate functions.

The Usage as given in Help:

... MIN( [DISTINCT] fdescriptor ).

Effect

For the lines selected, returns the smallest value in the column specified by the field label fdescriptor. The DISTINCT declaration does not affect the result. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is the NULL value.

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the lowest ticket price for each flight, ordered by customer name:

DATA: name TYPE scustom-name,

postcode TYPE scustom-postcode,

city TYPE scustom-city,

min TYPE sbook-loccuram.

SELECT scustom_{name scustom}postcode scustom~city

MIN( sbook~loccuram )

INTO (name, postcode, city, min)

FROM scustom INNER JOIN sbook

ON scustom_{id = sbook}customid

WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND

sbook~carrid = 'LH ' AND

sbook~connid = '0400'

GROUP BY scustom_{name scustom}postcode scustom~city

ORDER BY scustom~name.

WRITE: / name, postcode, city, min.

ENDSELECT.

... AVG( [DISTINCT] fdescriptor ).

Effect

For the lines selected, returns the mean of all the values in the column specified by the field label fdescriptor. You can only use AVG on a numeric-type field. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is the NULL value. Thus, when the mean is calculated, only those values not equal to NULL will be included.

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the mean ticket price for each flight, ordered by customer name:

DATA: name TYPE scustom-name,

postcode TYPE scustom-postcode,

city TYPE scustom-city,

average TYPE sbook-loccuram.

SELECT scustom_{name scustom}postcode scustom~city

AVG( sbook~loccuram ) AS avg

INTO (name, postcode, city, average)

FROM scustom INNER JOIN sbook

ON scustom_{id = sbook}customid

WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND

sbook~carrid = 'LH ' AND

sbook~connid = '0400'

GROUP BY scustom_{name scustom}postcode scustom~city

ORDER BY avg DESCENDING scustom~name.

WRITE: / name, postcode, city, average.

ENDSELECT.

Variant 4

... SUM( [DISTINCT] fdescriptor ).

Effect

Effect

For the lines selected, returns the sum of all the values in the column specified by the field label fdescriptor. You can only use SUM with a numeric-type field. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is the NULL value.

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the sum of ticket prices for each flight, ordered by customer name:

DATA: name TYPE scustom-name,

postcode TYPE scustom-postcode,

city TYPE scustom-city,

sum TYPE sbook-loccuram.

SELECT scustom_{name scustom}postcode scustom~city

SUM( sbook~loccuram )

INTO (name, postcode, city, sum)

FROM scustom INNER JOIN sbook

ON scustom_{id = sbook}customid

WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND

sbook~carrid = 'LH ' AND

sbook~connid = '0400'

GROUP BY scustom_{name scustom}postcode scustom~city

ORDER BY scustom~name.

WRITE: / name, postcode, city, sum.

ENDSELECT.

Variant 5

... COUNT( DISTINCT fdescriptor ).

Effect

For the lines selected, returns the number of different values in the column specified by the field label fdescriptor. The DISTINCT declaration is compulsory. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is 0.

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the number of different ticket prices for each flight, ordered by customer name:

DATA: name TYPE scustom-name,

postcode TYPE scustom-postcode,

city TYPE scustom-city,

count TYPE I.

SELECT scustom_{name scustom}postcode scustom~city

COUNT( DISTINCT sbook~loccuram )

INTO (name, postcode, city, count)

FROM scustom INNER JOIN sbook

ON scustom_{id = sbook}customid

WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND

sbook~carrid = 'LH ' AND

sbook~connid = '0400'

GROUP BY scustom_{name scustom}postcode scustom~city

ORDER BY scustom~name.

WRITE: / name, postcode, city, count.

ENDSELECT.

... MAX( [DISTINCT] fdescriptor ).

Effect

For the lines selected, returns the largest value in the column specified by the field label fdescriptor. The DISTINCT declaration does not affect the result. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is the NULL value.

Example

Displays a list of all the customers on all the Lufthansa 0400 flights in the year 2001, with the highest ticket price for each flight, ordered by customer name:

DATA: name TYPE scustom-name,

postcode TYPE scustom-postcode,

city TYPE scustom-city,

max TYPE sbook-loccuram.

SELECT scustom_{name scustom}postcode scustom~city

MAX( sbook~loccuram )

INTO (name, postcode, city, max)

FROM scustom INNER JOIN sbook

ON scustom_{id = sbook}customid

WHERE sbook~fldate BETWEEN '20010101' AND '20011231' AND

sbook~carrid = 'LH ' AND

sbook~connid = '0400'

GROUP BY scustom_{name scustom}postcode scustom~city

ORDER BY scustom~name.

WRITE: / name, postcode, city, max.

ENDSELECT.

REgards,

Ravi

[FredericGirod]

Hi,

this is a SQL statement,

Min (lowest value)

MAX (gretest value)

AVG (average value)

SUM (sum .....

you have to use the statement GROUP BY with that. Group by allow you to get a list of value and after you could make an opperation on this list of value. For example to get the gretest value with the MAX operation.

Rgd

Frédéric

[vinod_gunaware2]

( ( for each aggregate expression. The alias column name is used instead of the real name in the INTO and ORDER BY clauses. This is the only way of sorting by an aggregate expression in the ORDER BY clause.

If the list in the SELECT clause (excepting aggregate expressions) contains one or more field names, the field names must also be listed in the GROUP BY clause. The aggregate functions do not then apply to all of the selected lines, but to the individual groups of lines.

regards

vinod

[Former Member]

hai nikita,

if ur working with abap now u could go to se38abap editor-----open a report. type 'min' in it and press f1. you will get all the info about these aggregates.

MAX( [DISTINCT] col ) Determines the maximum value of the value in the column col in the resulting set or in the current group.

MIN( [DISTINCT] col ) Determines the minimum value of the content of the column col in the resulting set or in the current group.

AVG( [DISTINCT] col ) Determines the average value of the content of the column col in the resulting set or in the current group. The data type of the column has to be numerical.

SUM( [DISTINCT] col ) Determines the sum of the content of the column col in the resulting set or in the current group. The data type of the column has to be numerical.

COUNT( DISTINCT col ) Determines the number of different values in the column col in the resulting set or in the current group.

COUNT( * ) (or count(*)) Determines the number of rows in the resulting set or in the current group. No column label is specified in this case.

The aggregate functions evaluate the content of the groups defined by GROUP BY in the database system and transfer the result to the combined rows of the resulting set.

function MAX, MIN or SUM is the data type of the corresponding column in the ABAP Dictionary.

AVG have the data type FLTP, and those with COUNT have the data type INT4.

hope this is helpful.

regards,

praba.

[Former Member]

Hi Nikita,

MIN,MAX,AVG,SUM , COUNT keywords are used for reading the aggregate data.

MIN - returns the minimum value

MAX - returns the maximum value

AVG - returns the average value

SUM - returns the sum

Supposing that you want to find out the minimum value from all the records in the database , you can store the final value into a variable by writing MIN(field_name) and then assigning it to the variable of your choice.

For eg:

SELECT CARRID MIN( PRICE ) MAX( PRICE )

INTO (CARRID, MINIMUM, MAXIMUM)

FROM SFLIGHT

GROUP BY CARRID.

This statement finds out the minimum value and maximum value among all the records in the table SFLIGHT and stores them in MINIMUM & MAXIMUM respectively.

This can be extended to find SUM, AVG etc.

For more details , just refer the SAP Help docs.

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a46358411d1829f0000e829fbfe/content.htm

[hymavathi_oruganti]

the above functions only for numeric types

as the names implies MIN gives minimum, MAX gives MAXIMUM, AVG-----AVERAGE,

COUNT-------TOTAL NUMBER OF RECORS

SUM------DOES SUM