SQL计算出百分比

时间:2021-09-19 23:19:14

有clients和lead_sources俩表。mysql数据库。

lead_sources表结构类似:

SQL计算出百分比

clients表中的lead_source_id是外键。现在要统计某时间段内client内每种lead_source所占百分比

select 
a.L_name,
ROUND((a.L_sub_count*1.0/b.total_count*100),1) as perTotal, a.L_sub_count,
b.total_count
from ( SELECT LeadSource.name as L_name, count(*) as L_sub_count FROM clients as Client,lead_sources as LeadSource where Client.lead_source_id = LeadSource.id and LENGTH(Client.lead_source_id) > 0 and Client.created_date BETWEEN '2012-09-01' AND '2012-11-01' GROUP BY LeadSource.`name` order by L_sub_count desc ) a, ( select count(*) as Total_count FROM clients as Client,lead_sources as LeadSource where Client.lead_source_id = LeadSource.id and LENGTH(Client.lead_source_id) > 0 and Client.created_date BETWEEN '2012-09-01' AND '2012-11-01' ) b;

这个SQL有三个select,所以看成三部分,我的理解,后两个是构造虚表和字段。目的就是用于第一个select的查询。

结果类似:

SQL计算出百分比