Aggregate/ Scalar functions

create table tbl3 (sno varchar(10),m1 int,m2 int,total int,average int);

insert into tbl3 (sno,m1,m2) values ('1',10,20);

insert into tbl3 (sno,m1,m2) values ('2',30,40);

insert into tbl3 (sno,m1,m2) values ('3',50,60);

select * from tbl3;

UPDATE tbl3 SET total = m1+m2, average=total/2;

Aggregate functions

Aggregate functions operate against a collection of values, but return a single value.

Note: If used among many other expressions in the item list of a SELECT statement, the SELECT must have a GROUP BY clause!!

SELECT AVG(m1) FROM tbl3 WHERE m1>20;

SELECT COUNT(sno) FROM tbl3;

SELECT COUNT(*) FROM tbl3;

insert into tbl3 (sno,m1,m2) values ('3',5,60);

SELECT COUNT(DISTINCT sno) FROM tbl3;

/*SELECT FIRST(column) AS [expression] FROM table*/

SELECT FIRST(sno) AS lowest_m1 FROM tbl3 ORDER BY m1;

/*SELECT LAST(column) AS [expression] FROM table*/

SELECT LAST(m1) AS highest_m1 FROM tbl3 ORDER BY m1;

SELECT MAX(m1) FROM tbl3;

SELECT MIN(m1) FROM tbl3;

SELECT SUM(m1) FROM tbl3;

Scalar functions

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