May 2, 2008

Group functions in SQL, used for database testing

Group functions are functions applied to a group of rows. Group functions are:-

COUNT(*) - Returns the number of rows in the group.
MIN(exp) - Returns the minimum value of the expression evaluated on each row of the group.
MAX(exp) - Returns the maximum value of the expression evaluated on each row of the group.
AVG(exp) - Returns the average value of the expression evaluated on each row of the group

example:-

SELECT count(*)as Total,
min(userId)as minimum,
max(userId)as maximum,
avg(userid)as average
FROM users
will return this result as shown in image, result depends on table data. Non-group fields can not be mixed with group fields in the SELECT clause.

SELECT count(*)as Total,
min(userId)as minimum,
max(userId)as maximum,
avg(userid)as average,
userid FROM users

is an invalid query. Userid can not be used, because it is not under group fields.

No comments:

Search Here...

Popular Posts

Quick Test Professional