Accountancy-ncert-based-Accounting System Using Database Management System:SQL Aggregate Functions

SQL  Aggregate Functions
  The  SQL aggregate functions have the functionality similar to that of domain aggregate  function. However, unlike domain aggregate functions, these functions cannot be  called directly into controls used in Forms and Reports of Access. These  functions are used in SQL statements that provide the underlying record source  of Forms and Reports. All these functions, when used require the GROUP BY  clause in SQL statement :

(a)  Sum : This function is used to compute and return the sum of a set of values. For  Example, consider the following SQL statement that has been used in Chapter-V  to prepare the underlying information source of Trial Balance (Model-I.).

SELECT Debit As Code, Sum (Amount) As Total

FROM VOUCHERS

GROUP By Debit ;

In  the above SQL statement, Sum () has been used to compute the total amount by  which the transacted accounts have beeen debited.

(b)  Min and Max : These functions are used to retrieve respectively the minimum and  maximum of value set with respect to field or query expression. For Example,  the following SQL statement is capable of returning the amount of minimum and  maximum sales transaction in Model-I :
  SELECT Min (Amount) As MinSales, Max (Amount)  As MaxSales

FROM Vouchers

WHERE Credit = .811001. ;

It  may be noted that the sales account that is coded as .811001. is credited as and  when a sales transaction is recorded.

(c)  Count : This function counts the number of records returned by a query. The number  of times a sales transaction has occurred and recorded in books of accounts can  be known by executing the following SQL statement.
  SQL  statement.

SELECT  count (*)

FROM  Vouchers

WHERE  Credit = .811001.

In  the above SQL statement, the Credit field stores the account code of sales when  a sales transaction occurs. The WHERE clause restricts the number of records  returned by the above SQL to those in which credit field has the account code  of sales. Accordingly, the count () function returns the count value of records  returned by the above SQL statement.

(d)  First and Last : These functions are meant to retrieve the first and last  record
  of  a value set pertaining to a field or query expression.