GROUP BY
A SQL clause used to create frequency distributions when combined with any of the aggregate functions in a SELECT statement. GROUP BY is used to show some kind of information report, so an aggregate functions is a must.
Syntax:
SELECT FROM [WHERE [GROUP BY [HAVING [ORDER BY columnlist tablelist conditionlist ] columnlist ] conditionlist ] columnlist [ASC | DESC] ];
When using the GROUP BY clause with a SELECT statement:
• The SELECT’s columnlist must include a combination of column names and aggregate functions.
• The GROUP BY clause’s columnlist must include all nonaggregate function columns speci ed in the SELECT’s columnlist. If required, you could also group by any aggregate function columns that appear in the SELECT’s columnlist.
• The GROUP BY clause columnlist can include any columns from the tables in the FROM clause of the SELECT statement, even if they do not appear in the SELECT’s columnlist.
The product table:
+---------+--------------+------------+-------+-------+---------+------------+--------+ | P_CODE | P_DESCRIPT | P_INDATE | P_QOH | P_MIN | P_PRICE | P_DISCOUNT | V_CODE | +---------+--------------+------------+-------+-------+---------+------------+--------+ | BRT-345 | mewmew | 2015-10-04 | 85 | 10 | 4.50 | 0.07 | NULL | | BRT-356 | mewmew | 2012-12-20 | 86 | 12 | 3.50 | 0.20 | 1122 | | BRT-357 | Product2 | 2012-12-20 | 76 | 12 | 3.50 | 0.20 | 1122 | | BRT-358 | Product2_no2 | 2011-11-21 | 65 | 11 | 2.50 | 0.10 | NULL | | BRT-359 | Product2 | 2012-12-20 | 76 | 12 | 3.50 | 0.20 | 1122 | | BRT-360 | Product2 | 2012-12-20 | 76 | 12 | 3.50 | 0.20 | 1122 | +---------+--------------+------------+-------+-------+---------+------------+--------+
If you want to use query:
SELECT FROM GROUP BY V_CODE, P_CODE, P_DESCRIPT, P_PRICE PRODUCT V_CODE;
you generate a “not a GROUP BY expression” error. However, if you write the pre- ceding SQL command sequence in conjunction with an aggregate function(e.g count()), the GROUP BY clause works properly.
To show how many product each vendor supplied:
mysql> select V_CODE,COUNT(P_CODE) from PRODUCT GROUP BY V_CODE; +--------+---------------+ | V_CODE | COUNT(P_CODE) | +--------+---------------+ | NULL | 2 | | 1122 | 4 | +--------+---------------+ 2 rows in set (0.01 sec)
Note that the NULL will not be counted:
mysql> select V_CODE,COUNT(V_CODE) from PRODUCT GROUP BY V_CODE; +--------+---------------+ | V_CODE | COUNT(V_CODE) | +--------+---------------+ | NULL | 0 | | 1122 | 4 | +--------+---------------+ 2 rows in set (0.00 sec)
To calculate the Average price by per vendor:
mysql> select V_CODE, AVG(P_PRICE) FROM PRODUCT GROUP BY V_CODE; +--------+--------------+ | V_CODE | AVG(P_PRICE) | +--------+--------------+ | NULL | 3.500000 | | 1122 | 3.500000 | +--------+--------------+ 2 rows in set (0.00 sec)
HAVING CLAUSE
Having clause is similar to the where clause, However the WHERE clause applies to columns and expressions for individual rows, while the HAVING clause is applied to the out- put of a GROUP BY operation.
To show the total money you should pay to each vendor:
mysql> SELECT V_CODE, SUM(P_QOH*P_PRICE) AS TOTCOST FROM PRODUCT GROUP BY V_CODE; +--------+---------+ | V_CODE | TOTCOST | +--------+---------+ | NULL | 545.00 | | 1122 | 1099.00 | +--------+---------+ 2 rows in set (0.00 sec)
To show the vendor to which you paid totally more than 800:
mysql> SELECT V_CODE, SUM(P_QOH*P_PRICE) AS TOTCOST FROM PRODUCT GROUP BY V_CODE HAVING(TOTCOST>800); +--------+---------+ | V_CODE | TOTCOST | +--------+---------+ | 1122 | 1099.00 | +--------+---------+ 1 row in set (0.00 sec)
Join
The most import- ant distinction between a relational database and other databases. A join is performed when data is retrieved from more than one table at a time.
To get the correct result—that is, a natural join—you must select only the rows in which the common attribute values match.
The join condition is generally composed of an equality comparison between the foreign key and the primary key of related tables.
For example, suppose that you want to join the two tables VENDOR and PRODUCT. Because V_CODE is the foreign key in the PRODUCT table and the primary key in the VENDOR table, the link is established on V_CODE.
The Vendor table:
+--------+----------+-----------+------------+---------+---------+---------+ | V_CODE | V_NAME | V_CONTACT | V_AREACODE | V_PHONE | V_STATE | V_ORDER | +--------+----------+-----------+------------+---------+---------+---------+ | 1122 | GoodGuys | 0344112 | 301 | 045568 | SA | S | +--------+----------+-----------+------------+---------+---------+---------+
To join the two table Vendor and Product together:
mysql> SELECT * from PRODUCT,VENDOR WHERE PRODUCT.V_CODE=VENDOR.V_CODE; +---------+------------+------------+-------+-------+---------+------------+--------+--------+----------+-----------+------------+---------+---------+---------+ | P_CODE | P_DESCRIPT | P_INDATE | P_QOH | P_MIN | P_PRICE | P_DISCOUNT | V_CODE | V_CODE | V_NAME | V_CONTACT | V_AREACODE | V_PHONE | V_STATE | V_ORDER | +---------+------------+------------+-------+-------+---------+------------+--------+--------+----------+-----------+------------+---------+---------+---------+ | BRT-356 | mewmew | 2012-12-20 | 86 | 12 | 3.50 | 0.20 | 1122 | 1122 | GoodGuys | 0344112 | 301 | 045568 | SA | S | | BRT-357 | Product2 | 2012-12-20 | 76 | 12 | 3.50 | 0.20 | 1122 | 1122 | GoodGuys | 0344112 | 301 | 045568 | SA | S | | BRT-359 | Product2 | 2012-12-20 | 76 | 12 | 3.50 | 0.20 | 1122 | 1122 | GoodGuys | 0344112 | 301 | 045568 | SA | S | | BRT-360 | Product2 | 2012-12-20 | 76 | 12 | 3.50 | 0.20 | 1122 | 1122 | GoodGuys | 0344112 | 301 | 045568 | SA | S | +---------+------------+------------+-------+-------+---------+------------+--------+--------+----------+-----------+------------+---------+---------+---------+ 4 rows in set (0.01 sec)
When joining three or more tables, you need to specify a join condition for each pair of tables. e number of join conditions will always be n–1.
Remember, the join condition will match the foreign key of a table to the primary key of the related table.
Finally, be careful not to create circular join conditions.
Alias and Recursive query
An alias may be used to identify the source table from which the data is taken.
An alias is especially useful when a table must be joined to itself in a recursive query. For example, suppose that you are working with the EMP table shown in . Using the data in the EMP table, you can generate a list of all employees with their managers’ Employee number.
SELECT FROM WHERE ORDER BY E.EMP_NUM, E.EMP_LNAME, E.EMP_MGR, M.EMP_LNAME EMP E, EMP M E.EMP_MGR=M.EMP_NUM E.EMP_MGR;
