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 SQL: aggregate function, such as SUM, COUNT, MAX, and AVG. The fundamental difference between these functions and other functions is that they generally work on multiple records.

Select sum (population) FROM bbc

SUM is used in the population field of all returned records. The result is that only one result is returned for this query, that is, the total population of all countries.

BY using the group by clause, SUM and COUNT functions can be used for a GROUP of data. When you specify group by region, only one row of data belonging to the same region can be returned, that is, all fields except region (region) in the table, only one value can be returned after SUM, COUNT, and other aggregate function operations.

HAVING clause allows us to filter the data of each GROUP after grouping. The WHERE clause filters records before aggregation, that is, before the group by clause and HAVING clause.
The HAVING clause filters group records after aggregation.

Let's still understand the group by and HAVING clauses through specific instances, and use the bbc table introduced in section 3.

SQL instance:

1. display the total population and total area of each region:

SELECT region, SUM (population), SUM (area)
FROM bbc
Group by region

First, return records are divided into multiple groups BY region, which is the literal meaning of group. After grouping, Aggregate functions are used to calculate different fields (one or more records) in each group.

2. The total population and total area of each region are displayed. Only those regions with an area exceeding 1000000 square meters are displayed.

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 regions, because such a record does not exist in the table.

On the contrary, the HAVING clause allows us to filter the group data.

Group by clause. The syntax of HAVING is as follows:

SELECT "column 1", SUM ("column 2 ")
FROM "table name"
Group by "column 1"
HAVING (function condition)

Note: The group by clause is not required.

In our Store_Information table,

Store_Information table
Store_name Sales Date
Los Angeles $1500 Jan-05-1999
San Diego $250 Jan-07-1999
Los Angeles $300 Jan-08-1999
Boston $700 Jan-08-1999

 

We entered,

SELECT store_name, SUM (sales)
FROM Store_Information
Group by store_name
Having sum (sales)> 1500

Result:

Store_name SUM (Sales)
Los Angeles $1800

On this page, we list all the syntaxes that list SQL commands on this website. For more detailed instructions, click the command name.

SQL syntax
 

Select
SELECT "column" FROM "table name"

Distinct
Select distinct "column"
FROM "table name"

Where
SELECT "column"
FROM "table name"
WHERE "condition"

And/Or
SELECT "column"
FROM "table name"
WHERE "simple condition"
{[AND | OR] "simple condition"} +

In
SELECT "column"
FROM "table name"
WHERE "column" IN ('value 1', 'value 2 ',...)

Between
SELECT "column"
FROM "table name"
WHERE "column" between' value: 1' AND 'value: 2'

Like
SELECT "column"
FROM "table name"
WHERE "column" LIKE {mode}

Order
SELECT "column"
FROM "table name"
[WHERE "condition"]
Order by "column" [ASC, DESC]

Count
Select count ("column ")
FROM "table name"

Group
SELECT "column 1", SUM ("column 2 ")
FROM "table name"
Group by "column 1"

Having
SELECT "column 1", SUM ("column 2 ")
FROM "table name"
Group by "column 1"
HAVING (column)

Create Table
Create table "TABLE name"
("Column 1" "column 1 Data Type ",
"Column 2" "column 2 data types ",
...)

Drop Table
Drop table "TABLE name"

Truncate Table
Truncate table "TABLE name"

Insert
Insert into "table name" ("column 1", "column 2 ",...)
VALUES ("value 1", "value 2 ",...)

Update
UPDATE "table name"
SET "column 1" = [new value]
WHERE {condition}

Delete From
Delete from "table name"
WHERE {condition}