对包含聚合或子查询的表达式执行聚合函数

Question

不能执行任何类型的聚合函数。我有2个表与实体和一个表，其中包含每个实体的事务。

#qui
        +--------+------------+
        |  idx   | nbn        |
        +--------+------------+
        | 44444  | 152357     |    
        | 55555  | 778852     |
        | 66666  | 776856     |
        +--------+------------+
#zea
        +--------+------------+
        |  idx   | nbn        |
        +--------+------------+
        | 11111  | 159357     |    
        | 22222  | 753852     |
        | 33333  | 745856     |
        +--------+------------+
#trx
        +--------+------------+---------+--------+
        |  idx   | nbn        |trx_amt  |date    |
        +--------+------------+---------+--------+
        | 11111  | 159357     |100      |01-01-16|
        | 22222  | 753852     |200      |04-04-16|
        | 33333  | 745856     |300      |11-05-16|
        +--------+------------+---------+--------+

我的问题是：

    select 
      date
     ,qui_co = count (case when trx.idx in (select idx from qui) then trx_amt
     ,zea_co = count (case when trx.idx in (select idx from zea) then trx_amt 
end)
    from 
      trx 
        inner join (select idx from qui
                    union 
                    select idx from zea) xxx
        on trx.idx = xxx.idx
group by date

对其他解决方案有什么想法吗？

Answer 1

您应该能够使用left joins做到这一点：

select [date], count(q.idx), count(z.idx)
from trx t
left join qui q on t.idx=q.idx
left join zea z on t.idx=z.idx
group by [date]