如果使用Postgres或SQLite，可以使用Filter子句来提高可读性:

SELECT
  COUNT(1) FILTER (WHERE POSITION = 'Manager') AS ManagerCount,
  COUNT(1) FILTER (WHERE POSITION = 'Other') AS OtherCount
FROM ...

BigQuery也有Countif -在这里可以看到不同SQL方言对这些特性的支持: https://modern-sql.com/feature/filter

注意使用PrestoDB SQL(来自Facebook)，有一个快捷方式:

https://prestodb.io/docs/current/functions/aggregate.html

Count_if (x)→bigint 返回TRUE输入值的个数。这 函数等价于count(CASE WHEN x THEN 1 END)

在MySQL中，布尔表达式的计算结果为0或1，因此下面的聚合工作:

select sum(Position = 'Manager') as ManagerCount
from ...

你的意思是:

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

如果是这样，那么是的，这是可行的!

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

更多信息来自Technet

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

测试数据集

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')

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

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

假设在同一列中，你有经理，主管和团队领导的值，你可以得到每一个的计数如下所示:

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 ...