SQL PART 4 F

COUNT(DISTINCT column) Returns the number of distinct results

FIRST(column) Returns the value of the first record in the specified field

LAST(column) Returns the value of the last record in the specified field

MAX(column) Returns the highest value of a column

MIN(column) Returns the lowest value of a column

STDEV(column)

STDEVP(column)

SUM(column) Returns the total sum of a column

VAR(column)

VARP(column)

Scalar functions

Scalar functions operate against a single value, and return a single value based on the input value.

Useful Scalar Functions in MS Access

Function Description

UCASE(c) Converts a field to upper case

LCASE(c) Converts a field to lower case

MID(c,start[,end]) Extract characters from a text field

LEN(c) Returns the length of a text field

INSTR(c) Returns the numeric position of a named character within a text field

LEFT(c,number_of_char) Return the left part of a text field requested

RIGHT(c,number_of_char) Return the right part of a text field requested

ROUND(c,decimals) Rounds a numeric field to the number of decimals specified

MOD(x,y) Returns the remainder of a division operation

NOW() Returns the current system date

FORMAT(c,format) Changes the way a field is displayed

DATEDIFF(d,date1,date2) Used to perform date calculations

SQL GROUP BY and HAVING

Aggregate functions (like SUM) often need an added GROUP BY functionality.


GROUP BY...

GROUP BY... was added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called, and without the GROUP BY function it was impossible to find the sum for each individual group of column values.

The syntax for the GROUP BY function is:

SELECT column,SUM(column) FROM table GROUP BY column

GROUP BY Example

This "Sales" Table:

Company Amount

W3Schools 5500

IBM 4500

W3Schools 7100

And This SQL:

SELECT Company, SUM(Amount) FROM Sales

Returns this result:

Company SUM(Amount)

W3Schools 17100

IBM 17100

W3Schools 17100

The above code is invalid because the column returned is not part of an aggregate. A GROUP BY clause will solve this problem:

SELECT Company,SUM(Amount) FROM Sales

GROUP BY Company

Returns this result:

Company SUM(Amount)

W3Schools 12600

IBM 4500

HAVING...

HAVING... was added to SQL because the WHERE keyword could not be used against aggregate functions (like SUM), and without HAVING... it would be impossible to test for result conditions.

The syntax for the HAVING function is:

SELECT column,SUM(column) FROM table