SQL计算出百分比

小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

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

lead_sources表结构类似：

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;