以下是我的示例数据表:
ID | Status |
---|---|
1 | New |
1 | Processed |
2 | New |
2 | Processed |
3 | New |
3 | Processed |
4 | Processed |
5 | New |
我试图解决的是计算从"新"状态到"已处理"状态的转换率.从数据集中来看,只有ID 1、2和3满足了我的问题的要求,ID 4和5没有两个阶段.所以理论上,转换率应该是3/5*100%=60%.如何 Select 数据以计算具有"新建"和"已处理"状态的ID.
这是我try 过的代码,但我知道它是错误的,因为它提取了所有ID,但它们之间没有链接.
SELECT 'Conversion rate from Assigned enquiry to In progess leads' as 'Name', round((a.processed / b.new),3) * 100 as 'Answer'
FROM
(
SELECT cast(count(ID)as float) as processed
from table1
WHERE STATUS_ID = 'Processed'
) as a
cross join
(
SELECT cast(count(ID)as float) as new
from table_1
WHERE STATUS_ID = 'NEW'
) as b