如何在sqlquery中使用group by集群获取count()函数的总和 [英] How to get a sum of a count () funtction with using group by cluase in a sqlquery

问题描述

我有一个如下表

ID条件文档员工EID人员PID所有者状态
-------------------------------------------------- --------------------------
20 Condition1收益1处理器7 ccc Suzanne 1 LPTLC关闭
29 Condition2 Bank St Processor 7 Herity 30 LPTLC关闭
40 Condition4支付1处理器7插孔37 LPTLC关闭
65 Condition5 Paystub处理器7 David 47 LPTLC打开
81 Condition1 IAPLD处理器7 Herity 30 LPTLC已打开


我需要一个查询输出作为EID = 7且Status = Close的人数.
那意味着这些人仅具有关闭"状态，而没有打开"状态

来自ccc Suzanne，jack的结果为2.
这里的Herity具有打开和关闭状态，因此将其丢弃.
选择只具有状态字段的人员为关闭"的行，此指定的人员没有打开的状态.

I have a Table as below

ID Condition Document Employee EID Person PID Owner Status
----------------------------------------------------------------------------
20 Condition1 Payoff 1 Processor 7 ccc Suzanne 1 LPTLC Close
29 Condition2 Bank St Processor 7 Herity 30 LPTLC Close
40 Condition4 Payoff 1 Processor 7 jack 37 LPTLC Close
65 Condition5 Paystub Processor 7 David 47 LPTLC Open
81 Condition1 IAPLD Processor 7 Herity 30 LPTLC open


I need a query output as count of person where EID=7 and Status =Close .
Thats mean these persons have only status as Close, not Open

Result as 2 from ccc Suzanne,jack.
Here Herity Has status open and close so it discard.
select row which person has status field only Close,this specified person has no status open.
How can we get?

推荐答案

尝试一下-

Try this-

Select Count(PID) from (
SELECT 
  PID
FROM
  myTable
WHERE
  EID = 7
  group by PID
 having Min(Status)=Max(Status) and  Min(Status) = 'Close'
) as SelectedData;

进行编辑以获取总数[/编辑]

--Pankaj

Edited for getting total count[/Edit]

--Pankaj

尝试:

SELECT 
  COUNT(ID)
FROM
  myTable
WHERE
  EID = 7
  AND
  Status = 'Close'

这是什么问题?

What;s the problem with this?

这篇关于如何在sqlquery中使用group by集群获取count()函数的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！