count计算比例

[SylarZhou]

 写了个查询语句
select t3.FName,sum(case when isnull(t1.FLatePeriod,0)>0 and isnull(t1.FLatePeriod,0)<=12 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt1,
sum(case when isnull(t1.FLatePeriod,0)>12 and isnull(t1.FLatePeriod,0)<=24 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt2,
sum(case when isnull(t1.FLatePeriod,0)>24 and isnull(t1.FLatePeriod,0)<=36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt3,
sum(case when isnull(t1.FLatePeriod,0)>36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt4,
sum(isnull(t1.FLateAmt,0)) SumAmt,sum(isnull(t1.FVDebitAmt,0)) FVDebitAmt,
(count(case when isnull(t1.FLatePeriod,0)<>'0' then t1.fid end)/count(case when isnull(t4.FStatus,0)='1' then t4.fid end)) FYQB
from avw_1598 t1 inner join xd_cust t2 on t1.fid=t2.fid
inner join T_DepartMent t3 on t3.FItemID=t2.FDeptID
inner join XD_DKSQZS t4 on t1.fid=t4.fid 
group by t3.FName
其中最后的那个 count的计算得出的数据为0，但是我把两个count单独分出来执行
select count(case when isnull(FLatePeriod,0)<>'0' then fid end) from avw_1598
select count(case when isnull(FStatus,0)='1' then fid end) from XD_DKSQZS
得到的是 1519  47871 请高人指教 错误在何方

[叶子]

--没弄明白你说的是什么意思
SELECT  t3.FName ,
        SUM(CASE WHEN ISNULL(t1.FLatePeriod, 0) > 0
                      AND ISNULL(t1.FLatePeriod, 0) <= 12
                 THEN ISNULL(t1.FLateAmt, 0)
                 ELSE '0'
            END) FLateAmt1 ,
        SUM(CASE WHEN ISNULL(t1.FLatePeriod, 0) > 12
                      AND ISNULL(t1.FLatePeriod, 0) <= 24
                 THEN ISNULL(t1.FLateAmt, 0)
                 ELSE '0'
            END) FLateAmt2 ,
        SUM(CASE WHEN ISNULL(t1.FLatePeriod, 0) > 24
                      AND ISNULL(t1.FLatePeriod, 0) <= 36
                 THEN ISNULL(t1.FLateAmt, 0)
                 ELSE '0'
            END) FLateAmt3 ,
        SUM(CASE WHEN ISNULL(t1.FLatePeriod, 0) > 36
                 THEN ISNULL(t1.FLateAmt, 0)
                 ELSE '0'
            END) FLateAmt4 ,
        SUM(ISNULL(t1.FLateAmt, 0)) SumAmt ,
        SUM(ISNULL(t1.FVDebitAmt, 0)) FVDebitAmt ,
        ( COUNT(CASE WHEN ISNULL(t1.FLatePeriod, 0) <> '0' THEN t1.fid
                END) / COUNT(CASE WHEN ISNULL(t4.FStatus, 0) = '1' THEN t4.fid
                             END) ) FYQB
FROM    avw_1598 t1
        INNER JOIN xd_cust t2 ON t1.fid = t2.fid
        INNER JOIN T_DepartMent t3 ON t3.FItemID = t2.FDeptID
        INNER JOIN XD_DKSQZS t4 ON t1.fid = t4.fid
GROUP BY t3.FName

[SylarZhou]

[Quote=引用 1 楼 maco_wang 的回复:]

SQL code

--没弄明白你说的是什么意思

[/Quote]

( COUNT(CASE WHEN ISNULL(t1.FLatePeriod, 0) <> '0' THEN t1.fid
                END) / COUNT(CASE WHEN ISNULL(t4.FStatus, 0) = '1' THEN t4.fid
                             END) ) FYQB   就是这句语句执行得到的结果是0  但是他不应该是0

[叶子]

单独执行和放在里面是不一样的，在里面是 INNER JOIN ，内连接，就相当于多了很多where 条件。

[SylarZhou]

[Quote=引用 3 楼 maco_wang 的回复:]

单独执行和放在里面是不一样的，在里面是 INNER JOIN ，内连接，就相当于多了很多where 条件。
[/Quote]
已经解决了，我把count的数据类型转换成float 就得出百分比了

select t3.FName,sum(case when isnull(t1.FLatePeriod,0)>0 and isnull(t1.FLatePeriod,0)<=12 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt1,
sum(case when isnull(t1.FLatePeriod,0)>12 and isnull(t1.FLatePeriod,0)<=24 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt2,
sum(case when isnull(t1.FLatePeriod,0)>24 and isnull(t1.FLatePeriod,0)<=36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt3,
sum(case when isnull(t1.FLatePeriod,0)>36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt4,
sum(isnull(t1.FLateAmt,0)) SumAmt,sum(isnull(t1.FVDebitAmt,0)) FVDebitAmt,
(cast(count(case when isnull(t1.FLatePeriod,0)<>'0' then t1.fid end) as float)/cast(count(case when isnull(t4.FStatus,0)='1' then t4.fid end) as float)) FYQB
from avw_1598 t1 inner join xd_cust t2 on t1.fid=t2.fid
inner join T_DepartMent t3 on t3.FItemID=t2.FDeptID
inner join XD_DKSQZS t4 on t1.fid=t4.fid 
group by t3.FName

[叶子]

给你举个例子：

declare @T1 table (col int)
insert into @T1
select 1 union all
select 2 union all
select 3 union all
select 4
select 
COUNT(CASE WHEN ISNULL(t1.col, 0) <> '0' THEN t1.col END) from @T1 t1
/*
4
*/
declare @T2 table (col int)
insert into @T2
select 5 union all
select 6 union all
select 7 union all
select 8

select 
COUNT(CASE WHEN ISNULL(t2.col, 0) <> '0' THEN t2.col END) from @T2 t2
/*
4
*/
select 
COUNT(CASE WHEN ISNULL(t2.col, 0) <> '0' THEN t2.col END)/
(COUNT(CASE WHEN ISNULL(t1.col, 0) <> '0' THEN t1.col END)+1) from @T1 t1 INNER JOIN 
@T2 t2 ON t1.col=t2.col
/*
0
*/

[叶子]

declare @i int set @i=2
declare @j int set @j=3

select @i/@j /*0*/

select @i*1.00/@j /*0.6666666666666*/

[唐诗三百首]

try this,

select t3.FName,sum(case when isnull(t1.FLatePeriod,0)>0 and isnull(t1.FLatePeriod,0)<=12 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt1,
sum(case when isnull(t1.FLatePeriod,0)>12 and isnull(t1.FLatePeriod,0)<=24 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt2,
sum(case when isnull(t1.FLatePeriod,0)>24 and isnull(t1.FLatePeriod,0)<=36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt3,
sum(case when isnull(t1.FLatePeriod,0)>36 then isnull(t1.FLateAmt,0) else '0' end) FLateAmt4,
sum(isnull(t1.FLateAmt,0)) SumAmt,sum(isnull(t1.FVDebitAmt,0)) FVDebitAmt,
(cast(count(case when isnull(t1.FLatePeriod,0)<>'0' then t1.fid end) as decimal(9,5))
/count(case when isnull(t4.FStatus,0)='1' then t4.fid end)) FYQB
from avw_1598 t1 
inner join xd_cust t2 on t1.fid=t2.fid
inner join T_DepartMent t3 on t3.FItemID=t2.FDeptID
inner join XD_DKSQZS t4 on t1.fid=t4.fid 
group by t3.FName