SQL 是否可以在 Count() 中指定条件？

Is it possible to specify condition in Count()?

提问by agnieszka

Is it possible to specify a condition in Count()? I would like to count only the rows that have, for example, "Manager" in the Position column.

是否可以在 中指定条件Count()？我只想计算位置列中包含“经理”的行。

I want to do it in the count statement, not using WHERE; I'm asking about it because I need to count both Managers and Other in the same SELECT(something like Count(Position = Manager), Count(Position = Other))so WHEREis no use for me in this example.

我想在 count 语句中执行此操作，而不是使用WHERE; 我问这个问题，因为我需要计算两个经理和其他在同一个SELECT（像Count(Position = Manager), Count(Position = Other))这样WHERE在这个例子中没有用我。

回答by Guffa

If you can't just limit the query itself with a whereclause, you can use the fact that the countaggregate only counts the non-null values:

如果您不能仅使用where子句限制查询本身，则可以使用count聚合仅计算非空值的事实：

select count(case Position when 'Manager' then 1 else null end)
from ...

You can also use the sumaggregate in a similar way:

您还可以sum以类似的方式使用聚合：

select sum(case Position when 'Manager' then 1 else 0 end)
from ...

回答by RedFilter

Assuming you do not want to restrict the rows that are returned because you are aggregating other values as well, you can do it like this:

假设您不想限制返回的行，因为您也在聚合其他值，您可以这样做：

select count(case when Position = 'Manager' then 1 else null end) as ManagerCount
from ...

Let's say within the same column you had values of Manager, Supervisor, and Team Lead, you could get the counts of each like this:

假设在同一列中您有 Manager、Supervisor 和 Team Lead 的值，您可以像这样获得每个人的计数：

select count(case when Position = 'Manager' then 1 else null end) as ManagerCount,
    count(case when Position = 'Supervisor' then 1 else null end) as SupervisorCount,
    count(case when Position = 'Team Lead' then 1 else null end) as TeamLeadCount,
from ...

回答by Hivenfour

@Guffa 's answer is excellent, just point out that maybe is cleaner with an IF statement

@Guffa 的回答非常好，只是指出使用 IF 语句可能更干净

select count(IF(Position = 'Manager', 1, NULL)) as ManagerCount
from ...

回答by AdaTheDev

Depends what you mean, but the other interpretation of the meaning is where you want to count rows with a certain value, but don't want to restrict the SELECTto JUST those rows...

取决于您的意思，但对含义的另一种解释是您想要计算具有特定值的行，但不想将其限制SELECT为仅这些行...

You'd do it using SUM()with a clause in, like this instead of using COUNT(): e.g.

你可以使用SUM()in 子句来做，就像这样而不是使用COUNT()：例如

SELECT SUM(CASE WHEN Position = 'Manager' THEN 1 ELSE 0 END) AS ManagerCount,
    SUM(CASE WHEN Position = 'CEO' THEN 1 ELSE 0 END) AS CEOCount
FROM SomeTable

回答by Matthew Whited

You can also use the Pivot Keyword if you are using SQL 2005 or above

如果您使用的是 SQL 2005 或更高版本，也可以使用 Pivot 关键字

more infoand from Technet

更多信息和来自Technet

SELECT *
FROM @Users
PIVOT (
    COUNT(Position)
    FOR Position
    IN (Manager, CEO, Employee)
) as p

Test Data Set

测试数据集

DECLARE @Users TABLE (Position VARCHAR(10))
INSERT INTO @Users (Position) VALUES('Manager')
INSERT INTO @Users (Position) VALUES('Manager')
INSERT INTO @Users (Position) VALUES('Manager')
INSERT INTO @Users (Position) VALUES('CEO')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')
INSERT INTO @Users (Position) VALUES('Employee')

回答by Dana

Do you mean just this:

你的意思是这样吗：

SELECT Count(*) FROM YourTable WHERE Position = 'Manager'

If so, then yup that works!

如果是这样，那行得通！

回答by z00l

I know this is really old, but I like the NULLIFtrick for such scenarios, and I found no downsides so far. Just see my copy&pasteable example, which is not very practical though, but demonstrates how to use it.

我知道这真的很旧，但我喜欢NULLIF这种场景的技巧，到目前为止我没有发现任何缺点。看看我的 copy&pasteable 示例，虽然它不是很实用，但演示了如何使用它。

NULLIFmight give you a small negative impact on performance, but I guess it should still be faster than subqueries.

NULLIF可能会对性能产生很小的负面影响，但我想它仍然应该比子查询更快。

DECLARE @tbl TABLE ( id [int] NOT NULL, field [varchar](50) NOT NULL)

INSERT INTO @tbl (id, field)
SELECT 1, 'Manager'
UNION SELECT 2, 'Manager'
UNION SELECT 3, 'Customer'
UNION SELECT 4, 'Boss'
UNION SELECT 5, 'Intern'
UNION SELECT 6, 'Customer'
UNION SELECT 7, 'Customer'
UNION SELECT 8, 'Wife'
UNION SELECT 9, 'Son'

SELECT * FROM @tbl

SELECT 
    COUNT(1) AS [total]
    ,COUNT(1) - COUNT(NULLIF([field], 'Manager')) AS [Managers]
    ,COUNT(NULLIF([field], 'Manager')) AS [NotManagers]
    ,(COUNT(1) - COUNT(NULLIF([field], 'Wife'))) + (COUNT(1) - COUNT(NULLIF([field], 'Son'))) AS [Family]
FROM @tbl

Comments appreciated :-)

评论赞赏:-)

回答by Peter

SELECT COUNT(*) FROM bla WHERE Position = 'Manager'

回答by NawaMan

I think you can use a simple WHERE clause to select only the count some record.

我认为你可以使用一个简单的 WHERE 子句来只选择一些记录的计数。

回答by user3029478

Here is what I did to get a data set that included both the total and the number that met the criteria, within each shipping container. That let me answer the question "How many shipping containers have more than X% items over size 51"

这是我为获得一个数据集所做的工作，该数据集包括每个集装箱内的总数和符合条件的数量。这让我回答了“有多少集装箱有超过 X% 的物品超过 51 号”的问题

select
   Schedule,
   PackageNum,
   COUNT (UniqueID) as Total,
   SUM (
   case
      when
         Size > 51 
      then
         1 
      else
         0 
   end
) as NumOverSize 
from
   Inventory 
where
   customer like '%PEPSI%' 
group by
   Schedule, PackageNum