MySQL: Group By

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;