汇总查询

1字段函数

求和
所有销售人员的总定额和总销售量

mysql > SELECT SUM (QUOTA), SUM (SALES) FROM SALESREPS;
+ -- ----------+------------+
| SUM (QUOTA) | SUM (SALES) |
+ -- ----------+------------+
| 2700000.00 | 2892487.00 |
+ -- ----------+------------+

平均值
制造商ACI生产的产品的平均价格

mysql > SELECT AVG (PRICE) FROM PRODUCTS WHERE MFR_ID = ' ACI ' ;
+ -- ----------+
| AVG (PRICE) |
+ -- ----------+
| 804.285714 |
+ -- ----------+

最大值与最小值：字段中的数据可以包含数字、字符串或日期，函数的结果与字段中数据的数据类型完全相同
求所分配的最小和最大定额

mysql > SELECT MIN (QUOTA), MAX (QUOTA) FROM SALESREPS;
+ -- ----------+------------+
| MIN (QUOTA) | MAX (QUOTA) |
+ -- ----------+------------+
| 200000.00 | 350000.00 |
+ -- ----------+------------+

计数数据值
价值超过25000美元的订单数

mysql > SELECT COUNT (AMOUNT) FROM ORDERS WHERE AMOUNT > 25000.00 ;
+ -- -------------+
| COUNT (AMOUNT) |
+ -- -------------+
| 4 |
+ -- -------------+

count(*)会包含值为NULL的记录

mysql > SELECT COUNT ( * ), COUNT (SALES), COUNT (QUOTA) FROM SALESREPS;
+ -- --------+--------------+--------------+
| COUNT ( * ) | COUNT (SALES) | COUNT (QUOTA) |
+ -- --------+--------------+--------------+
| 10 | 10 | 9 |
+ -- --------+--------------+--------------+
mysql > SELECT SUM (SALES), SUM (QUOTA),( SUM (SALES) - SUM (QUOTA)), SUM (SALES - QUOTA) FROM SALESREPS;
+ -- ----------+------------+-------------------------+------------------+
| SUM (SALES) | SUM (QUOTA) | ( SUM (SALES) - SUM (QUOTA)) | SUM (SALES - QUOTA) |
+ -- ----------+------------+-------------------------+------------------+
| 2892487.00 | 2700000.00 | 192487.00 | 117502.00 |
+ -- ----------+------------+-------------------------+------------------+

其中SUM(SALES-QUOTA)记录了9条记录的和
   SUN(SALES)记录了10条记录，而SUM(QUOTA)记录了9条记录，一条位NULL。
销售人员具有的头衔数目。

mysql > SELECT COUNT ( DISTINCT TITLE) FROM SALESREPS;
+ -- ---------------------+
| COUNT ( DISTINCT TITLE) |
+ -- ---------------------+
| 3 |
+ -- ---------------------+

 

2分组查询

 

求每名销售人员的平均订单大小

mysql > SELECT REP, AVG (AMOUNT) FROM ORDERS GROUP BY REP;
+ -- ----+--------------+
| REP | AVG (AMOUNT) |
+ -- ----+--------------+
| 101 | 8876.000000 |
| 102 | 5694.000000 |
| 103 | 10800.000000 |

每个销售点分配了几个销售人员

mysql > SELECT REP_OFFICE, COUNT ( * ) FROM SALESREPS GROUP BY REP_OFFICE;
+ -- ----------+----------+
| REP_OFFICE | COUNT ( * ) |
+ -- ----------+----------+
| NULL | 1 |
| 11 | 2 |

多个分组字段
计算每名销售人员的每位客户的订单总和。

mysql > SELECT REP,CUST, SUM (AMOUNT) FROM ORDERS GROUP BY REP,CUST;
+ -- ----+------+-------------+
| REP | CUST | SUM (AMOUNT) |
+ -- ----+------+-------------+
| 101 | 2102 | 3978.00 |
| 101 | 2108 | 150.00 |
| 101 | 2113 | 22500.00 |

WITH ROLLUP与GROUP BY组合使用，WITH ROLLUP会使得GROUP BY操作显示每级分组的小计。
计算每位销售人员的每位客户的订单总和，给出每位销售人员的小计。

mysql > SELECT REP,CUST, SUM (AMOUNT) FROM ORDERS GROUP BY REP,CUST WITH ROLLUP;
+ -- ----+------+-------------+
| REP | CUST | SUM (AMOUNT) |
+ -- ----+------+-------------+
| 101 | 2102 | 3978.00 |
| 101 | 2108 | 150.00 |
| 101 | 2113 | 22500.00 |
| 101 | 小计 | 26628.00 |
| 102 | 2106 | 4026.00 |

WITH CUBE则更进一步，显示出分组字段的每个可能组合的小计。
计算每名销售人员的每位客户的订单总和，给出每位销售人员和每位客户的小计。
mysql> SELECT REP,CUST,SUM(AMOUNT) FROM ORDERS GROUP BY REP,CUST WITH CUBE;
ERROR 1235 (42000): This version of MySQL doesn't yet support 'CUBE'
这个版本的MYSQL不支持CUBE，哈哈。。。。
ORACLE中GROUP BY ROLLUP或GROUP BY CUBE后面跟分组字段列表，且必须用括号括起，如GROUP BY CUBE(REP,CUST)。
GROUP BY会为查询结果自动排序，后面接ORDER BY会将这种排序自动覆盖掉。

3分组查询的限制(MYSQL没有这种限制，书上有这种说法，我不知道这个是针对那种DBMS产品而言的，还是注意一下吧)

 

分组查询的一个限制，当分析分组查询的合法性时，SQL乎略关于主键和外键的信息，如：
计算每名销售人员的订单总和
SELECT EMPL_NUM,NAME,SUM(AMOUNT) FROM ORDERS,SALESREPS WHERE REP=EMPL_NUM GROUP BY EMPL_NUM;
ERROR:"NAME" not a GROUP BY expression
销售人员的雇员编号进行分组事实上与按销售人员的名字进行分组是一样的。分组字段EMPL_NUM是SALESREPS表的主键，所以NAME字段对于每个组来说必须是单值的。

mysql > SELECT EMPL_NUM,NAME, SUM (AMOUNT) FROM ORDERS,SALESREPS WHERE REP = EMPL_NUM GROUP BY EMPL_NUM,NAME;
+ -- --------+---------------+-------------+
| EMPL_NUM | NAME | SUM (AMOUNT) |
+ -- --------+---------------+-------------+
| 101 | Dan Roberts | 26628.00 |
| 102 | Sue Smith | 22776.00 |

4分组搜索条件

 

求出其订单总和超过30000美元的销售人员的平均订单大小

mysql > SELECT REP, AVG (AMOUNT) FROM ORDERS GROUP BY REP HAVING SUM (AMOUNT) > 30000 ;
+ -- ----+--------------+
| REP | AVG (AMOUNT) |
+ -- ----+--------------+
| 105 | 7865.400000 |
| 106 | 16479.000000 |

 

对具有两位或多为销售人员的销售点，计算在此工作的所有销售人员的总定额和总销售量

mysql > SELECT CITY, SUM (QUOTA), SUM (SALESREPS.SALES) FROM OFFICES,SALESREPS WHERE OFFICE = REP_OFFICE GROUP BY CITY HAVING COUNT ( * ) >= 2 ;
+ -- -----------+------------+----------------------+
| CITY | SUM (QUOTA) | SUM (SALESREPS.SALES) |
+ -- -----------+------------+----------------------+
| Chicage | 775000.00 | 734997.00 |
| Los Angeles | 700000.00 | 835915.00 |
| New York | 575000.00 | 692637.00 |
+ -- -----------+------------+----------------------+

 

显示订单总量超过其库存量75%的每项产品的单价、库存量、订单总量。

mysql > SELECT DESCRIPTION,PRICE,QTY_ON_HAND, SUM (QTY) FROM PRODUCTS,ORDERS WHERE MFR = MFR_ID AND PRODUCT = PRODUCT_ID GROUP BY MFR_ID,PRODUCT_ID,DESCRIPTION,PRICE,QTY_ON_HAND HAVING SUM (QTY) > (. 75 * QTY_ON_HAND) ORDER BY QTY_ON_HAND DESC ;
+ -- ---------------+---------+-------------+----------+
| DESCRIPTION | PRICE | QTY_ON_HAND | SUM (QTY) |
+ -- ---------------+---------+-------------+----------+
| Widget Adjuster | 25.00 | 37 | 30 |
| Right Hinge | 4500.00 | 12 | 15 |
| 500 - lb Brace | 1425.00 | 5 | 22 |
+ -- ---------------+---------+-------------+----------+

 

 

 

 

转载于:https://www.cnblogs.com/sachie/archive/2010/11/24/1886097.html