topic Re: Aggregate Function in Application Development and Automation Discussions

Aggregate Function

Former Member — Fri, 02 May 2008 12:47:37 GMT

Hi ,

Can AnyOne Give Example for Agrregate Function...

Using Max , Min , and Sum , Count .....

Some Coded Programs... Will be better

Re: Aggregate Function

Former Member — Fri, 02 May 2008 12:54:37 GMT

MAX Returns the greatest value in the column determined by the database field f for the selected lines. Specifying DISTINCT does not change the result. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL .

MIN Returns the smallest value in the column determined by the database field f for the selected lines. Specifying DISTINCT does not change the result. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL .

AVG Returns the average value in the column determined by the database field f for the selected lines. AVG can only apply to a numeric field. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL .

SUM Returns the sum of all values in the column determined by the database field f for the selected lines. SUM can only apply to a numeric field. NULL values are ignored unless all values in a column are NULL values. In this case, the result is NULL .

COUNT Returns the number of different values in the column determined by the database field f for the selected lines. Specifying DISTINCT is obligatory here. NULL values are ignored unless all values in a column are NULL values. In this case, the result is 0.

COUNT( * ) Returns the number of selected lines. If the SELECT command contains a GROUP BY clause , it returns the number of lines for each group. The form COUNT(*) is also allowed.

If ai is a field f , MAX( f ) , MIN( f ) or SUM( f ) , the corresponding column of the result set has the same ABAP/4 Dictionary format as f . With COUNT( f ) or COUNT( * ) , the column has the type INT4 , with AVG( f ) the type FLTP .

If you specify aggregate functions together with one or more database fields in a SELECT clause, all database fields not used in one of the aggregate functions must be listed in the GROUP-BY clause .

Output all flight destinations for Lufthansa flights from Frankfurt:

TABLES SPFLI.
DATA   TARGET LIKE SPFLI-CITYTO.

SELECT DISTINCT CITYTO
       INTO TARGET FROM SPFLI
       WHERE
         CARRID   = 'LH '       AND
         CITYFROM = 'FRANKFURT'.
  WRITE: / TARGET.
ENDSELECT.

Output the number of airline carriers which fly to New York:

TABLES SPFLI.
DATA   COUNT TYPE I.

SELECT COUNT( DISTINCT CARRID )
       INTO COUNT FROM SPFLI
       WHERE
         CITYTO = 'NEW YORK'.
WRITE: / COUNT.

Output the number of passengers, the total weight and the average weight of luggage for all Lufthansa flights on 28.02.1995:

TABLES SBOOK.
DATA:  COUNT TYPE I, SUM TYPE P DECIMALS 2, AVG TYPE F.
DATA:  CONNID LIKE SBOOK-CONNID.

SELECT CONNID COUNT( * ) SUM( LUGGWEIGHT ) AVG( LUGGWEIGHT )
       INTO (CONNID, COUNT, SUM, AVG)
       FROM SBOOK
       WHERE
         CARRID   = 'LH '      AND
         FLDATE   = '19950228'
       GROUP BY CONNID.
  WRITE: / CONNID, COUNT, SUM, AVG.
ENDSELECT.

I hope it helps.

Best Regards,

Vibha

Please mark all the helpful answers

Re: Aggregate Function

Sm1tje — Fri, 02 May 2008 12:55:45 GMT

SELECT MAX( fieldx ) FROM database table INTO lv_max WHERE = condition.

Same for Min, SUM and COUNT.

Re: Aggregate Function

Former Member — Fri, 02 May 2008 13:04:22 GMT

Hi Kalyan,

SQL Aggregate Functions

SQL Aggregate functions perform calculations in an SQL query. Aggregates are most often used in combination with a Group By clause.

Below are the SQL aggregate functions available in an MS Access query:

Aggregate

Function Description

Select Sum Total (count) of the field values

Select Avg Average of the field values

Select Min Lowest (minimum) field value

Select Max Highest (maximum) field value

Select Count Count of the values other than nulls

Select StDev Standard deviation of the field values including date/time fields

Select Var Variance of the field values including date/time

Here are some examples of aggregate function usage:

Select Count Aggregate Function Query

Select Count(Emp_ID) From M_Employees;

The query above simply counts the autonumber field in the M_Employees table.

Select Average Aggregate Function Example

Select Avg(Emp_Salary) From M_Employees Where Emp_Age<50;

The above aggregate query determines the average salary for employees under 50 years of age.

The SQL aggregate query below gets a little interesting by showing you how to answer more complex question of your data:

M_Employees ID Emp_Name Emp_Salary Emp_Age

1 Joe $18.00 51

2 billy $17.00 52

3 Molly $16.00 53

4 bobby $15.00 41

5 robert $14.00 42

6 milly $13.00 43

7 harry $12.00 44

8 ed $11.00 45

9 sally $10.00 46

SELECT avg(iif(emp_age>=50,Emp_Salary,null)) as Over_50_Salary,avg(iif(emp_age<50,emp_salary,null)) as Under_50_Salary

FROM M_Employees;

Over_50_Salary Under_50_Salary

$17.00 $12.50

Note the use of the immediate if (iif) to bracket the results and return two columns where the would normally be only one.

Note there is no Group By which is normally associated with an SQL aggregate function query.

Note we are using aliases (as) to generate our own column names.

Note nulls are skipped in the average aggregate and that is what makes this query work.

Select StDev Query

Using the Employee table shown above now we get the standard deviation of the salary column:

SELECT StDev([Emp_Salary]) AS Salary_Standard_Deviation FROM M_Employees;

Results are pretty simple:

SQL Select Var Aggregate Variance Query

SELECT Var([Emp_Salary]) AS Salary_Variance FROM M_Employees;

The results are shown below:

Here are some additional techniques to consider...

When creating a report it is often useful to have the Count Average and Standard Deviation listed at the bottom of each column of numbers. As fairly simple technique is to use a series of union queries to build the result rows.

The first query would retrieve all the raw numbers in a multi row list.

The second union query would append the aggregate count function.

The third union query would add the aggregate average function. Finally the last union select would add the standard deviation.

Another technique to try out is to use the aggregate functions in the scalar query.

More Aggregate Function Information:

Domain Aggregate Functions in MS Access

SQL Aggregate Function Examples: Dlookup, Dmin, Dmax, Dlast, Dfirst, DAvg, DSum, Dcount, DStdev, DstdevP,Dvar, DvarP.

www.blueclaw-db.com/domain_aggregate_function/

Advanced Union Query Download

In this example we selecting the individual data records from the table and at the same time using the union query function to select SQL Aggregate functions ...

www.blueclaw-db.com/download/union_query_advanced.htm

for abap aggregate function see these links

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

http://help.sap.com/saphelp_nw70/helpdata/en/fc/eb3990358411d1829f0000e829fbfe/content.htm

thanks

sagar

pls reward me if usefull

Re: Aggregate Function

Former Member — Fri, 02 May 2008 13:05:41 GMT

Hello,

Max :

Min:

Sum:

Count:

****Reward for all useful answers