Previous | Home | Next |
Group clause summarize the result set in to a groups defined in the query using aggregate function.
SELECT 'max'=MAX (discount) FROM discountsSELECT column1, column2, ... column_n, aggregate_function (expression) FROM table_name WHERE conditions GROUP BY ALL column1, column2, ... column_n
SELECT 'max'=MAX (discount) FROM discountsSELECT column1, column2, ... column_n, aggregate_function (expression) FROM table_name WHERE conditions GROUP BY ALL column1, column2, ... column_n
expressions: describe the column name (s) or expressions on which the result set of the SELECT statement is to be grouped.
ALL is a keyword used to include those groups that do not meet the search condition.
Example
SELECT type, 'avg'=AVG(advance) FROM titles WHERE type LIKE 'b%' GROUP BY ALL type
Output
type | Range |
business | 5000.0000 |
mod_cook | NULL |
popular_comp | NULL |
psychology | NULL |
trad_cook | NULL |
UNDECIDED | NULL |
Return all type from table but the 'avg' display only those type which started with ' b'. Means this keyword used to display all groups, including those exclude from WHERE clause. The ALL keyword is meaningful for those query that contain WHERE clause.
Example
SELECT type,'pub Id'=pub_id, 'avg'=AVG (price) FROM titles GROUP BY type,pub_id
Return the 'type' and 'pub Id' and 'avg' , which calculated from titles table.
Example
SELECT type, 'avg'=AVG(advance) FROM titles WHERE title_id IN ('BU1032','PC1035') GROUP BY ALL type
Output
type | Range |
business | 5000.0000 |
mod_cook | NULL |
popular_comp | 7000.0000 |
psychology | NULL |
trad_cook | NULL |
UNDECIDED | NULL |
Previous | Home | Next |