SQL 必知必会第九课 汇总数据

SQL 必知必会第九课 汇总数据

# 平均函数用法
select avg(prod_price) as avg_price  from products;
select avg(prod_price) as avg_price  from products where vend_id = 'DLL01';

# 计数函数用法
select count(*) as num_cust from customers; -- 对customer的行进行计数
select count(cust_email) as num_cust from Customers; -- 对cus列的邮箱进行计数

## 最大值函数
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 avg(distinct prod_price) as avg_price from products where vend_id = 'DLL01';

##聚类不同值
select count(*) as num_items, min(prod_price) as price_min, max(prod_price) as price_max, avg(prod_price) as price_avg from products;

## challenges
select sum(quantity) as item_ordered from OrderItems; -- 1

select sum(quantity) as item_ordered from OrderItems where prod_id = 'BR01'; -- 2

select max(prod_price) as max_price from products where prod_price <=  10; -- 3

注意点

1. avg( ), count( ), max( ), min( ), sum( ) 忽略null值，count(*)不忽略null值

2. max，min可以返回数值以及日期最大值或者最小值， 运用于文本时， 他会返回文本数据排序的最后一行或者最前面的行

3. 聚类函数中包含不同值用distinct函数，但distinct只支持列，不支持行，即不可用于count(*)