Mysql-6 Data Retrieval (4)

Summarize 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 value of a column and

1. Avg function

SELECT AVG (Prod_price) as avg_price from products;

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 case, Cust_email is calculated 3 times, indicating that only three customers in 5 customers have e-mail

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

20005;

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

Grouped Data GROUP BY

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 have COUNT (*) >=2;

SELECT Prod_price,vend_id,count (*) as Num_prods from Products WHERE prod_price >= GROUP by vend_id have CO UNT (*) >=2;

SELECT vend_id, COUNT (*) as num_prods from the products GROUP by vend_id have COUNT (*) >=2;

Order by 1, sort produced output 2, any column can use 3, do not necessarily need

Group by 1, grouped rows, but the output may not be in the order of grouping 2, only select columns or expression columns may be used, and each selection column expression 3 must be used, if the column is used with the aggregation function, you must use the

Where filter row, having filter group

SELECT order_num, SUM (quantity*item_price) as OrderTotal from OrderItems GROUP by Order_num have SUM (quantity*item_pric e) >=;

SELECT order_num, SUM (quantity*item_price) as OrderTotal from OrderItems GROUP by Order_num have SUM (quantity*item_pric e) >= ORDER by OrderTotal;

Working with sub-queries

' TNT2 ' ; SELECT cust_id from Orders WHERE order_num in (20005,20007'TNT2 ');

SELECT AVG (prod_price) *As Avg_price from the 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 the products GROUP by vend_id; SELECT cust_id from Orders; SELECT cust_id, COUNT (*) as orders from orders GROUP by cust_id have COUNT (*) >=2; SELECT Prod_price,vend_id,count (*) as Num_prods from products WHERE Prod_price >=TenGROUP by vend_id have COUNT (*) >=2; SELECT vend_id, COUNT (*) as Num_prods from Products GROUP by vend_id have COUNT (*) >=2; SELECT order_num, SUM (Quantity*item_price) as OrderTotal from OrderItems GROUP by Order_num have SUM (quantity*item_price) >= -; SELECT order_num, SUM (Quantity*item_price) as OrderTotal from OrderItems GROUP by Order_num have SUM (quantity*item_price) >= -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');

Mysql-6 Data Retrieval (4)