SQL Statement tutorial (11) Group
Now let's go back to the function. Remember to use
SUM
This command is used to calculate all Sales (turnover! What should we do if we want to calculate the turnover (sales) of each store (store_name? In this case, we need to do two things: first, we need to select both store_name and Sales columns. Second, we need to confirm that all sales are calculated separately based on each store_name. The syntax is:
SELECT "column 1", SUM ("column 2") FROM "table name" group by "column 1"
In our demonstration
Store_Information table
Store_name |
Sales |
Date |
Los Angeles |
$1500 |
Jan-05-1999 |
San Francisco |
$300 |
Jan-08-1999 |
Boston |
$700 |
Jan-08-1999 |
Let's get
SELECT store_name, SUM (Sales) FROM Store_Information group by store_name
Result
:
Store_name
SUM (Sales)
Los Angeles $1800
San Diego $250
Boston $700
When we select more than one column and at least one column contains the function, we need to use the group by command. In this case, we need to determine that we have all other columns of group. In other words, we need to place the column including the function in the group by clause.
SQL
Statement tutorial
(12) HAVING
Then weHow can we set conditions for the values produced by functions?For example, we may only need to know which stores have a turnover of more than $1,500. In this case, we cannot use
WHERE
. What should we do? Fortunately, SQL provides
HAVING
And we can use this command to achieve this goal.
HAVING
Clauses are usually at the end of an SQL sentence. An SQL statement containing the HAVING clause does not necessarily contain
GROUP
Clause. HAVING
Syntax:
SELECT "column 1", SUM ("column 2") FROM "table name" group by "column 1" HAVING (function condition)
Note:GROUPClause is not required.
In the example of our Store_Information table
Store_Information table
Store_name |
Sales |
Date |
Los Angeles |
$1500 |
Jan-05-1999 |
San Francisco |
$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
Result
:
Store_name
SUM (Sales)
Los Angeles $1800