mysql - 计数不正确 "signups"

Question

select date_format(payout.date, '%m/%d/%Y') as Date,
    (select commission.PAYOUTID from commission
    where commission.status <> "reversed"
    and commission.payoutid = payout.payoutid
    group by commission.payoutid) 
    as PayoutID, 

    convert(payout.acctid,char) as Account,
    payout.total_amount as Commission,

   (select count(commission_tracker.CAMPAIGNID) as 'Signups' from 
   commission_tracker
   where commission_tracker.COMMISSIONTRACKERID = PayoutID) 
   as Signups,

1. 上面的最后一个嵌套就是问题所在。不算数

2. 我似乎无法让它返回正确的注册数量因为可能格式化。

3. 如有任何帮助，我们将不胜感激

   (select count(account.ACCTID) as 'Dupes' from account
      where account.type = 'customer_duplicate'
      and account.acctid = Signups) 
   as Dupes
   
   from payout
   where payout.payout_method = 'paypal'
   and payout.payout_status = 'processing'
   order by Commission desc
   

Answer 1

尽量避免 select 1, (select...), (select...), etc... from table。这种嵌套永远无法正常运作。最好使用 sum(case when truecondition then 1 else 0 end)

 select sum(case when  account.type = 'customer_duplicate'
              and account.acctid = 'Signups' then 1 else 0 end) as fieldname
from payout
where payout.payout_method = 'paypal'
and payout.payout_status = 'processing'

在你的自学文件中，永远不要在选择行中使用子选择作为一个好的规则。子选择在 from 子句中很棒，在选择行中很糟糕

只是一个编辑...请记住，如果您有其他字段，在这种情况下 sum() 将需要一个分组依据

select 
date_format(payout.date, '%m/%d/%Y') as Date,
sum(case when  account.type = 'customer_duplicate'
              and account.acctid = 'Signups' then 1 else 0 end) as fieldname
from payout
where payout.payout_method = 'paypal'
and payout.payout_status = 'processing'
group by date

底部分组依据将按日期对结果进行分组。如果您想显示按日期分组的结果，salesman_name 那么您还需要按 salesman_name 分组。