CODEWITHSUNDEEP
sql-server
user1741131
user1741131

Reputation: 11

SQL select count case comparing 2 columns

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.

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.

Upvotes: 0

Views: 5646

Answers (5)

Magic
Magic

Reputation: 159

SELECT count(*),CASE WHEN col1=col2 THEN 'same' ELSE 'different' END AS x
FROM theTable
group by CASE WHEN col1=col2 THEN 'same' ELSE 'different' END

I am using Oracle, it do not allow me to group by x (alias name) so I am grouping by CASE statement and it works fine.

Upvotes: 0

philkos
philkos

Reputation: 1

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

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

Upvotes: 0

Mladen Prajdic
Mladen Prajdic

Reputation: 15677

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$]

Upvotes: 2

Tad
Tad

Reputation: 944

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

SUM(result) might be what you are looking for

Upvotes: 3

hardmath
hardmath

Reputation: 8833

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

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

Upvotes: 0

Related Questions