MySQL：《学习笔记与实战》之简单聚合操作（1）group by order by limits

SELECT count(date) FROM fbd_store where date>'2018-07-03';
SELECT from_web,count(date) FROM fang where date>'2018-07-03' group by from_web;

#01-总价区间房源统计
SELECT total as '总价区间' ,count(date) as '发布条数',(count(date)/(SELECT COUNT(date) FROM fang))
as '发布占比' 
from  fang 
GROUP BY  total
ORDER BY  count(date) DESC ;


#02-单价区间房源统计
SELECT price as '单价区间' ,count(date) as '发布条数',(count(date)/(SELECT COUNT(date) FROM fang))
as '发布占比' 
from  fang
GROUP BY  price 
ORDER BY  count(date) DESC ;


#03-面积区间房源统计
SELECT area as '面积区间' ,count(date) as '发布条数',(count(date)/(SELECT COUNT(date) FROM fang))
as '发布占比' 
from  fang 
GROUP BY  area
ORDER BY  count(date) DESC;


# 04-区域房源统计 只显示前20名
SELECT region as '区域名称' ,count(date) as '发布条数',(count(date)/(SELECT COUNT(date) FROM fang))
as '发布占比' 
from  fang 
GROUP BY  region 
ORDER BY count(date) desc limit 20 ;


# 05-02-主打区域-综合  单独查询 单独查询 单独查询 单独查询 单独查询
SELECT 
region as '区域名称',
location as '地段名称',
total as '总价区间' ,
price  as '单价区间' ,
area as '面积区间' ,
agent_company as '公司名称' ,
count(date) as '发布条数'
from  fang
WHERE region='武侯'or region='锦江' or region='成华'
GROUP BY  region,location,area,agent_company 
ORDER BY region desc, location desc,
area asc ;