SQL Group by and having

SQL GROUP BY and having

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.

GROUP by clause. The having syntax is as follows:

SELECT "Field 1", SUM ("Field 2")
From "Table name"
GROUP by "Field 1"
Having (function condition)

Please note that the GROUP by clause is not necessarily required.

In the example of our store_information table,

Store_information table
Store_name Sales Date
Los Angeles $1500 jan-05-1999
San Diego $ jan-07-1999
Los Angeles $300 jan-08-1999
Boston $700 jan-08-1999

We enter,

SELECT store_name, SUM (sales)
From Store_information
GROUP by Store_name
Having SUM (sales) > 1500

Results:

Store_name SUM (Sales)
Los Angeles $1800

On this page, we list all the syntax for listing SQL directives on this site. For a more detailed description, click the command name.

SQL syntax

Select
SELECT "field" from "Table name"

Distinct
SELECT DISTINCT "Field"
From "Table name"

Where
SELECT "Field"
From "Table name"
WHERE "Condition"

and/or
SELECT "Field"
From "Table name"
WHERE "Simple Condition"
{[And|or] "Simple condition"}+

In
SELECT "Field"
From "Table name"
WHERE "Field" in (' Value 1 ', ' Value 2 ', ...)

Between
SELECT "Field"
From "Table name"
WHERE "Field" BETWEEN value 1 ' and ' Value 2 '

Like
SELECT "Field"
From "Table name"
WHERE "field" like {mode}

ORDER BY
SELECT "Field"
From "Table name"
[WHERE ' condition ']
Order BY "Field" [ASC, DESC]

Count
SELECT COUNT ("field")
From "Table name"

Group by
SELECT "Field 1", SUM ("Field 2")
From "Table name"
GROUP by "Field 1"

Having
SELECT "Field 1", SUM ("Field 2")
From "Table name"
GROUP by "Field 1"
Having (field)

Create Table
CREATE table "Table name"
("Field 1" "Field 1 data Type",
"Field 2" "Field 2 Data category",
... )

Drop Table
DROP table "Table name"

Truncate Table
TRUNCATE table "Table name"

Insert into
INSERT into "table name" ("Field 1", "Field 2", ...)
VALUES ("Value 1", "Value 2", ...)

Update
UPDATE "Table name"
SET "Field 1" = [New value]
WHERE {Condition}

Delete from
DELETE from "Table name"
WHERE {Condition}