About group by and having (database)

Introduced GROUP BY and HAVING clauses, first talk about a special function in the SQL language: aggregate functions such as SUM, COUNT, MAX, AVG, etc.. These functions and other functions of the fundamental difference is that they are generally many records.

SELECT SUM (population) FROM BBC

Here SUM function in all returns records on the population field, the result is that the query returns only one result, that is, the total population of all countries.

By using the GROUP BY clause that allows the SUM and COUNT function works belongs to a group of data. When you specify a GROUP BY Region, a set of data belong to the same region (region) will only return one row value, that is, all fields outside the region (region), only polymerization by SUM, COUNT, etc. function operations return a value.

HAVING clause allows us to filter into the group after each set of data, the WHERE clause to filter records before polymerization is the role before the GROUP BY and HAVING clauses.

HAVING clause to filter the set of records after polymerization.

Note that: the group of the same name as the group by will merge the data output. The table below (Checks):

CHECKNUM PAYEE AMOUNT REMARKS
1 Ma Bell 150 Have sons next time
2 Reading RR. 245.34 train to Chicago
3 Ma Bell 200.33 Cellular Phone
16 Cash 25 Wild Night Out
17 Joans Gas 25.1 Gas
8 Cash 60 Trip to Boston
21 Cash 34 Trip to Dayton
25 Joans Gas 15.75 Gas

> SELECT PAYEE, SUM (AMOUNT), COUNT (PAYEE) FROM CHECKS
> GROUP BY payee;

After the execution results:

Payee SUM COUNT
Ma Bell 350.34 2

Reading RR. 245.34 1
Cash 119 3
Joans Gas 40.85 2
: When our group on multiple columns, for example, run:

> SELECT PAYEE, SUM (AMOUNT), COUNT (PAYEE) FROM CHECKS
> GROUP BY PAYEE, REMARKS;

The result is not the same, will not be merged output, because of the type of transactions recorded in the REMARKS column, can not be combined.