在SQL子查询中聚合计数

基本上,我希望查询1给我查询2的结果。我需要使用查询1 b / c。我还有其他子查询可添加要计算或获取最大日期的位置。

ClientVisit.client_id在查询1中尝试了“分组依据” ,但出现此错误'ClientVisit.clientvisit_id',因为选择列中的列未包含在聚合函数或GROUP BY子句中,所以该无效。ClientVisit.clientvisit_id不在选择列表中。

查询1

SELECT ClientVisit.client_id ,
       ( SELECT COUNT( ZFORM_group_note_28.clientvisit_id )
         FROM ZFORM_group_note_28
         WHERE ZFORM_group_note_28.successful_day_752410 = 'Yes'
               AND ZFORM_group_note_28.clientvisit_id =        ClientVisit.clientvisit_id ) AS Successful_Day
FROM ClientVisit
WHERE ClientVisit.visittype_id = 16
      AND ClientVisit.program_id = 5
      AND ClientVisit.rev_timein >= @param1
      AND ClientVisit.rev_timeout < DATEADD( DAY ,
                                         1 ,
                                         @param2 )

查询2

SELECT ClientVisit.client_id ,
       COUNT( ZFORM_group_note_28.clientvisit_id ) AS Successful_Day
FROM ClientVisit
INNER JOIN ZFORM_group_note_28
ON ZFORM_group_note_28.clientvisit_id = ClientVisit.clientvisit_id
WHERE ClientVisit.visittype_id = 16
      AND ClientVisit.program_id = 5
      AND ZFORM_group_note_28.successful_day_752410 = 'Yes'
      AND ClientVisit.rev_timein >= @param1
      AND ClientVisit.rev_timeout < DATEADD( DAY ,
                                         1 ,
                                         @param2 )
GROUP BY ClientVisit.client_id

结果:查询1

client_id   successful_day
1182    1
1182    1
1379    1
1379    1
1379    1
1379    1

查询2

client_id   successful_day
1182    2
1379    9
1523    2
1757    1
穆纳夫瓦尔

也许这会有所帮助。(通过使用case语句修改了查询)

select ClientVisit.client_id, sum(Successful_Day) as Successful_Day,Sum(Unsuccessful_Day) as Unsuccessful_Day
from
(
 SELECT ClientVisit.client_id ,
 Case when ZFORM_group_note_28.successful_day_752410 = 'Yes' then  COUNT(ZFORM_group_note_28.clientvisit_id ) else 0 end AS Successful_Day,
 Case when ZFORM_group_note_28.successful_day_752410 = 'No' then  COUNT(ZFORM_group_note_28.clientvisit_id ) else 0 end AS Unsuccessful_Day 
 FROM ClientVisit
 INNER JOIN ZFORM_group_note_28
 ON ZFORM_group_note_28.clientvisit_id = ClientVisit.clientvisit_id
 WHERE ClientVisit.visittype_id = 16
  AND ClientVisit.program_id = 5
  AND ClientVisit.rev_timein >= @param1
  AND ClientVisit.rev_timeout < DATEADD( DAY ,  1 , param2 )
 GROUP BY ClientVisit.client_id
)Clients
Group by ClientVisit.client_id

本文收集自互联网,转载请注明来源。

如有侵权,请联系 [email protected] 删除。

编辑于
0

我来说两句

0 条评论
登录 后参与评论

相关文章