SQL select count case比较2列

I'm breaking my head over a SQL statement comparing 2 columns and when they are not similar the statement should add 1 to the count.

我在比较2列的SQL语句上打破了我的头脑，当它们不相似时，语句应该为计数加1。

Here is my code.

这是我的代码。

SELECT 
    COUNT(*) CASE WHEN Column1 = Column2 THEN '0' ELSE '1' END AS result 
FROM [Sheet1$] 
GROUP BY Column1

What am I doing wrong? I get the error message of a missing operator in the query expression.

我究竟做错了什么？我在查询表达式中收到了缺少运算符的错误消息。

4 个解决方案

#1

COUNT(*) is going to count the rows regardless of the value in result.

无论结果中的值如何，COUNT（*）都将计算行数。

SUM(result) might be what you are looking for

SUM（结果）可能正是您要找的

#2

this should do it:

这应该这样做：

SELECT 
    SUM(CASE WHEN Column1 = Column2 THEN 0 ELSE 1 END) AS NumberOfDiffs,
    SUM(CASE WHEN Column1 = Column2 THEN 1 ELSE 0 END) AS NumberOfEquals
FROM [Sheet1$]

#3

SELECT count(*)
WHERE NOT (Column1 = Column2)
FROM [Sheet1$]

will count how many rows don't have equal columns.

将计算有多少行没有相等的列。

#4

If you want the count the number of absent days from a attendance table use the following query, D1 is the first day, D2 is the second day...and so on

如果您想从出勤表中计算缺席天数，请使用以下查询，D1是第一天，D2是第二天......依此类推

SELECT     TOP (200) PayrollYear, PayrollMonth, EmployeeId, EmployeeName, JobNo, SUM(CASE WHEN D1 = 'A' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN D2 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D3 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D4 = 'A' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN D5 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D6 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D7 = 'A' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN D8 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D9 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D10 = 'A' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN D11 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D12 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D13 = 'A' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN D14 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D15 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D16 = 'A' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN D17 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D18 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D19 = 'A' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN D20 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D21 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D22 = 'A' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN D23 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D24 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D25 = 'A' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN D26 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D27 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D28 = 'A' THEN 1 ELSE 0 END) 
                      + SUM(CASE WHEN D29 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D30 = 'A' THEN 1 ELSE 0 END) + SUM(CASE WHEN D31 = 'A' THEN 1 ELSE 0 END) 
                      AS TotAbsent
FROM         T_EmpAttendance
GROUP BY PayrollYear, PayrollMonth, EmployeeId, EmployeeName, JobNo

#1