The GROUP BY clause is used to group sets of rows into groups and returns only one row from each group. It is mostly used with aggregate functions but it does not have to.
SELECT column_name1,column_name2,aggregate_function(column_name3),... FROM table_name WHERE conditions GROUP BY column_name3;
GROUP BY with aggregate functions
AVG – average value of a column
SUM – sum value of a column
MAX- maximum value of a column
MIN – minimum value of a column
COUNT – count how many in the group
Aggregate functions perform some calculation on each group formed by GROUP BY and return a single value.
How to get number of vendors in each vendor group?
GROUP BY with expression
GROUP BY with HAVING
HAVING is used to filter the groups formed by GROUP BY. WHERE filters each row in the result set. HAVING filters each group in the groups formed by GROUP BY.
Applying HAVING with a criteria of average price of less than 1000.
If GROUP BY is omitted then HAVING is bahaving like the WHERE clause.
HAVING and GROUP BY can be used together to query statistical data about your data like finding the average order or the average number of dollars spent by a certain group of customers.
GROUP BY with ROLL UP
ROLL UP is used with GROUP BY to calculate subtotals and the grand total for groups form by GROUP BY.