Mysql-6 data retrieval (4), mysql-6 Data Retrieval

Mysql-6 data retrieval (4), mysql-6 Data Retrieval
Summary data

Function	Description
AVG ()	Returns the average of a column.
COUNT ()	Returns the number of rows in a column.
MAX ()	Returns the maximum value of a column.
MIN ()	Returns the minimum value of a column.
SUM ()	Returns the sum of values in a column.

1. AVG Functions

SELECT AVG(prod_price) AS avg_price FROM products;

SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;

2. count () function

SELECT COUNT(*) AS num_cust FROM customers;

SELECT COUNT(cust_email) AS num_cust FROM customers;

This select statement uses count (cust_email) to count the values in the cust_email column. In this example, cust_email is calculated three times, indicating that only three of the five customers have emails.

3. max () function

SELECT MAX(prod_price) AS max_price FROM products;

4. min () function

SELECT MIN(prod_price) AS min_price FROM products;

5. sum () function

SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;

SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;

SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products;

 

Group Data group

SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id;

SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;

SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;

SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;

Order by 1, sorting output 2, any column can be used 3, not necessarily required

Group by 1. group rows, but the output may not be the group order. 2. Only select columns or expression columns are allowed, in addition, each selection column expression must be used. 3. If columns are used together with clustering functions, columns must be used.

Where filters rows and having Filters

SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;

SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;

 

Use subquery

 

SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';SELECT cust_id FROM orders WHERE order_num IN (20005,20007);SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');

 

 

SELECT AVG(prod_price) *AS avg_price FROM products;SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id = 1003;SELECT COUNT(*) AS num_cust FROM customers;SELECT COUNT(cust_email) AS num_cust FROM customers;SELECT MAX(prod_price) AS max_price FROM products;SELECT MIN(prod_price) AS min_price FROM products;SELECT SUM(quantity) AS items_ordered FROM orderitems WHERE order_num = 20005;SELECT SUM(item_price*quantity) AS total_price FROM orderitems WHERE order_num = 20005;SELECT COUNT(*) AS num_items,MIN(prod_price) AS price_min, MAX(prod_price) AS price_max,AVG(prod_price) AS avg_price FROM products;SELECT vend_id ,COUNT(*) AS num_prods FROM products GROUP BY vend_id;SELECT cust_id FROM orders ;SELECT cust_id ,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*)>=2;SELECT prod_price,vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >=10 GROUP BY vend_id HAVING COUNT(*)>=2;SELECT vend_id, COUNT(*) AS num_prods FROM products GROUP BY vend_id HAVING COUNT(*)>=2;SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50;SELECT order_num ,SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num HAVING SUM(quantity*item_price)>=50 ORDER BY ordertotal;SELECT order_num FROM orderitems WHERE prod_id = 'TNT2';SELECT cust_id FROM orders WHERE order_num IN (20005,20007);SELECT cust_id FROM orders WHERE order_num IN (SELECT order_num FROM orderitems WHERE prod_id = 'TNT2');