Simple SQL Tutorial GROUP by and having

Source: Internet
Author: User
Tags sql tutorial

Before introducing the group BY and having clauses, we must first talk about a special function in the SQL language: aggregate functions, such as SUM, COUNT, MAX, AVG, and so on. The fundamental difference between these functions and other functions is that they generally work on more than one record.
SELECT SUM (population) from BBC

The sum action here is on all population fields that return records, and the result is that the query returns only one result, the total population of all countries.

By using the GROUP BY clause, you can have the sum and COUNT functions work on data that belongs to a group. When you specify group by region, a set of data that belongs to the same region (region) will only return one row of values, that is, all fields except region (region) in the table can only return a value after the aggregate functions such as SUM, count, and so on.

The HAVING clause allows us to filter the groups of data after which the WHERE clause filters the records before aggregation. That is, the effect is before the GROUP BY clause and the HAVING clause.
The HAVING clause filters the group records after aggregation.

Let's take a concrete example to understand the group BY and having clauses, as well as the BBC table introduced in section Iii.

SQL instance:

Show the total population and area of each area:

SELECT region, sum (population), sum (area)
From BBC
GROUP by region

First, you divide the return records into groups by region, which is the literal meaning of group by. After the group is finished, the different fields (one or more records) in each group are calculated with aggregate functions.

Show the total population number and area of each area. Show only those areas with an area of more than 1000000.
SELECT region, sum (population), sum (area)
From BBC
GROUP by region
Having SUM (area) >1000000

Here, we cannot use where to filter more than 1000000 of the region because no such record exists in the table.

Instead, the HAVING clause allows us to filter the groups of data after the group.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.