educoder 数据库系统概论 SQlserver聚合函数

1.AVG（）函数的使用

USE Mall
GO

SET NOCOUNT ON

------ return two columns that the price bigger than average price ------
-- ********** Begin ********** --
select prod_name,prod_price
from Products
where prod_price > (select avg(prod_price) from Products)



-- ********** End ********** --

GO

2.COUNT（）函数的使用

USE Mall
GO

SET NOCOUNT ON

------ return the number of product which price bigger than 10 -----
-- ********** Begin ********** --
select count(prod_price) 
from Products
where prod_price>10



-- ********** End ********** --

GO

3.MAX（）和MIN（）函数的使用

USE Mall
GO

SET NOCOUNT ON

------ return the price of the least expensive item ------
-- ********** Begin ********** --
select prod_name,prod_price
from Products
where prod_price = (select min(prod_price)from Products)


-- ********** End ********** --

GO

 4.SUM（）函数的使用

USE Mall
GO

SET NOCOUNT ON

------ return the amount of all products ------
-- ********** Begin ********** --
select sum(prod_price*quantity) as amount
from products



-- ********** End ********** --

GO