如何计算sql中特定列的数量？

I have a table called Employees which has lots of columns but I only want to count some specific columns of this table.

我有一个名为Employees的表,它有很多列,但我只想计算这个表的一些特定列。

i.e. EmployeeID: 001

即EmployeeID:001

week1: 40

week2: 24

Week3: 24

Week4: 39

This employee (001) has two weeks below 32. How do I use the COUNT statement to calculate that within these four weeks(columns), how many weeks(columns) have the value below 32?

此员工(001)有两周以下的32周。如何使用COUNT语句计算在这四周(列)内,有多少周(列)的值低于32?

3 个解决方案

#1

Something like this should do it:

这样的事情应该这样做:

SELECT EmployeeID, 
    (CASE WHEN Week1 < 32 THEN 1 ELSE 0 END +
     CASE WHEN Week2 < 32 THEN 1 ELSE 0 END +
     CASE WHEN Week3 < 32 THEN 1 ELSE 0 END +
     CASE WHEN Week4 < 32 THEN 1 ELSE 0 END) AS Count
  FROM Employees ...

#2

SELECT COUNT(*)
FROM [TableName];

You can use the

你可以使用

WHERE statement after SELECT ... FROM ... to specify conditions such as Employee's with hours below 32.

SELECT ... FROM ...之后的WHERE语句,用于指定小时数低于32的Employee的条件。

For example:

SELECT COUNT(*)
FROM [TableName]
WHERE `Hours`<'32';

Use the AND operator to add more conditions

使用AND运算符添加更多条件

SELECT COUNT(*)
FROM [TableName]
WHERE `Hours`<'32' AND `EmployeeName`='Todd';

#3

-1

SELECT 'MEMBERS(ACTIVE ACCOUNTS)' particular, ''value
union all
SELECT +'    '+ member_type,  
    (SELECT active_students = (SELECT COUNT(member_type_id) FROM LM_MEMBER_MASTER 
     WHERE institute_code = 'GDAB' AND member_type_id = A.member_type_id AND status = 'Active'))
FROM LM_MEMBER_TYPE_MASTER A WHERE institute_code = 'GDAB' 
union all
Select '    Total' particular,  COUNT(member_type_id) FROM LM_MEMBER_MASTER 
WHERE institute_code = 'GDAB'  AND status = 'Active'
union all
SELECT 'MEMBERS(CLOSED ACCOUNTS)' particular, '' value
union all
SELECT +'    '+member_type,  
    (SELECT active_students = (SELECT COUNT(member_type_id) FROM LM_MEMBER_MASTER 
     WHERE institute_code = 'GDAB' AND member_type_id = A.member_type_id AND status = 'Closed'))
FROM LM_MEMBER_TYPE_MASTER A WHERE institute_code = 'GDAB' 
union all
Select '    Total' particular,  COUNT(member_type_id) FROM LM_MEMBER_MASTER 
WHERE institute_code = 'GDAB'  AND status = 'Closed'

in this code i dont want to count 'MEMBERS(ACTIVE ACCOUNTS)' and 'MEMBERS(CLOSED ACCOUNTS)' bcoz its headlines

在这个代码中我不想数'成员(活跃账户)'和'成员(关闭账户)'bcoz的头条新闻

#1