1. Group By
1) GroupBy Is usually used with aggragation function(statistic function). If not, group by is pointless.
2) Five aggragation function:
1) max
2) min
3) sum
4) avg
5) count
Eg.
# fetch the most expensive goods price
# knowing the process of fetching max
select max(shop_price) from goods;
select max(market_price - shop_price) from goods;
# select goods_id, goods_name, max(shop_price) from goods;
# the above will not work correctly
# this is a syntax error in Oracle/SQLServer
# fetch the most expensive goods price in each category
select cat_id, max(shop_price) from goods group by cat_id;
# cat_id makes sense as we are grouping by cat_id;
# fetch the max goods_id
select max(goods_id) from goods;
# fetch the min goods_id
select min(goods_id) from goods;
# fetch the cheapest shop_price
select min(shop_price) from goods;
# count the sum of goods_number
select sum(goods_number) from goods;
# count the sum of goods_number in each category
select cat_id, sum(goods_number) from goods group by cat_id;
# calculate the average shop_price
select avg(shop_price) from goods;
# count the variety of goods (different line of goods)
# count the registered user today
# is also applicable in paging: calculate the number of different lines in order to paging
select count(goods_id) from goods;
select count(*) from goods;
# count the variety of goods in each cat
select cat_id, count(*) from goods group by cat_id;
Comment:
1) Regard column name as variable:
select cat_id, count(*) from goods group by cat_id;
cat_id can be seen as variable.
variable can use operator to calculating.
Eg
# Regard column name as variable
# Fetch the difference between shop_price and market_price
select (market_price - shop_price) from goods;
# Fetch the total money in each category
select cat_id, sum(goods_num * shop_price) from goods group by cat_id;
# Alias for a certain column
select cat_id, sum(goods_num * shop_price) as cash_repository from goods group by cat_id;
# How should we represent the total money?
select sum(goods_num * shop_price) from goods;