时间戳

首页 > 解决方案 > 如何根据Id获取计数

sql-server - 如何根据Id获取计数

问题描述

在我的存储过程中，我有一个临时表@Branches。我正在向其中插入值@Branches，表结构如下：

declare @Branches table( BranchId int, BranchName varchar(60))

BranchID    BranchName  IsActive
--------------------------------
16          New Delhi       1
17          Panjab          0

在我的数据库中有一个名为的表lobby，其数据如下，

QueueID FkBranch    IsActive    Status  AddedLocalTime  FkAssistTypeID
553279  16              1           5   7/12/2019           2
553278  16              1           5   7/12/2019           1
553277  16              1           5   7/12/2019           1
553276  16              1           5   7/12/2019           1
553275  16              1           5   7/12/2019           2
553274  16              1           5   7/9/2019            2

我需要FkAssistTypeID根据它的值来计算它，我重新使用了这个脚本

declare @BranchDetail table (Id int, Name varchar(60), TotalInteraction float, AssistCount float)

insert into @BranchDetail
    select b.BranchId as Id, b.BranchName as Name, 
    count(lo.LobbyId) TotalInteraction,
    count(case WHEN lo.FkAssistTypeID = 1 then 1 end) as AssistCount
from 
    @Branches b
left outer join
    (select br.BranchId, l.LobbyId, l.FkAssistTypeID
     from lobby l 
     left outer join @Branches br on l.FkBranchId = br.BranchId
     where l.AddedLocalTime >= @startDate 
       and l.AddedLocalTime <= CONVERT(VARCHAR, @endDate, 101) + ' 23:59:59'
       and l.IsActive = 1 
    group by br.BranchId, l.LobbyId) lo on lo.BranchId = b.BranchId 
group by 
    b.BranchId, b.BranchName
order by 
    b.BranchName

select @AvgInteractions= COALESCE( Convert(decimal(18,2), AVG(TotalInteraction)),0) from @BranchDetail

update @BranchDetail
SET AverageInteractions =@AvgInteractions from @BranchDetail

select * from @BranchDetail

我收到此错误

消息 8120，级别 16，状态 1，过程 spGetActComDetails，第 228 行 [批处理开始第 7 行]
列“lobby.FkAssistTypeId”在选择列表中无效，因为它不包含在聚合函数或 GROUP BY 子句中。

但是如果我在上面的查询中使用它的所有地方都删除FkAssistTypeID，查询工作正常并检索此输出。

在此处输入图像描述

但我需要这个

在此处输入图像描述

如何解决这个问题？

标签： sql-servertsqljoinstored-procedurescount

解决方案

尝试：

insert into @BranchDetail
select b.BranchId as Id,b.BranchName as Name,count(lo.LobbyId) TotalInteraction,
SUM(case WHEN lo.FkAssistTypeID = 1 THEN 1 ELSE 0 END) as AssistCount
from @Branches b
left outer join
(
select  br.BranchId,l.LobbyId,l.FkAssistTypeID
from lobby l 
left outer join @Branches br on l.FkBranchId=br.BranchId
where  l.AddedLocalTime >=@startDate and ( l.AddedLocalTime ) <= CONVERT(VARCHAR, @endDate, 101)+ ' 23:59:59'and l.IsActive=1
group by br.BranchId,l.LobbyId,l.FkAssistTypeID) lo on lo.BranchId=b.BranchId 
group by b.BranchId,b.BranchName
order by b.BranchName

您需要SUM改用COUNT，因为COUNT：

COUNT(*) 返回组中的项目数。这包括 NULL 值和重复项。

推荐阅读