In last week’s puzzle SQL Puzzle: RIGHT without using the RIGHT function we looked at how to do a RIGHT function without using the RIGHT function. Today we are going to find out how many columns are even in a row.

Before starting I want you to be aware that 0 is an even number From wikipedia: Parity of zero

Zero is an even number. In other words, its parity—the quality of an integer being even or odd—is even. Zero fits the definition of “even number”: it is an integer multiple of 2, namely 0 × 2. As a result, zero shares all the properties that characterize even numbers: 0 is divisible by 2, 0 is surrounded on both sides by odd numbers, 0 is the sum of an integer (0) with itself, and a set of 0 objects can be split into two equal sets.

Zero also fits into the patterns formed by other even numbers. The parity rules of arithmetic, such as even − even = even, require 0 to be even. Zero is the additive identity element of the group of even integers, and it is the starting case from which other even natural numbers are recursively defined. Applications of this recursion from graph theory to computational geometry rely on zero being even. Not only is 0 divisible by 2, it is divisible by every integer. In the binary numeral system used by computers, it is especially relevant that 0 is divisible by every power of 2; in this sense, 0 is the “most even” number of all.

Here is the table that you will use for your solution

T-SQL |

1
2
3
4
5
6
7
8
| CREATE TABLE #Puzzle(Col1 int, Col2 int, Col3 int, Col4 int, Col5 int)
INSERT #Puzzle VALUES (1,2,3,4,5)
INSERT #Puzzle VALUES (0,1,2,3,4)
INSERT #Puzzle VALUES (2,2,2,2,2)
INSERT #Puzzle VALUES (1,1,1,1,1)
INSERT #Puzzle VALUES (3,2,3,2,3)
INSERT #Puzzle VALUES (2,3,2,3,2) |

CREATE TABLE #Puzzle(Col1 int, Col2 int, Col3 int, Col4 int, Col5 int)
INSERT #Puzzle VALUES (1,2,3,4,5)
INSERT #Puzzle VALUES (0,1,2,3,4)
INSERT #Puzzle VALUES (2,2,2,2,2)
INSERT #Puzzle VALUES (1,1,1,1,1)
INSERT #Puzzle VALUES (3,2,3,2,3)
INSERT #Puzzle VALUES (2,3,2,3,2)

And here is the expected output

Col1 Col2 Col3 Col4 Col5 Even
1 2 3 4 5 2
0 1 2 3 4 3
2 2 2 2 2 5
1 1 1 1 1 0
3 2 3 2 3 2
2 3 2 3 2 3

That should be pretty easy right?

How about a solution that doesn’t use a CASE statement, you think you can do it (I can think of 2 ways without using a CASE statement)

Post your solutions as a comment

## About the Author

Denis has been working with SQL Server since version 6.5. Although he worked as an ASP/JSP/ColdFusion developer before the dot com bust, he has been working exclusively as a database developer/architect since 2002. In addition to English, Denis is also fluent in Croatian and Dutch, but he can curse in many other languages and dialects (just ask the SQL optimizer) He lives in Princeton, NJ with his wife and three kids.

## 11 Comments

The simple and easy solution is to use the modulus operator on each column.

Select Col1, Col2, Col3, Col4, Col5,

5 – (Col1 % 2 + Col2 % 2 + Col3 % 2 + Col4 % 2 + Col5 % 2) As Even

From #Puzzle

Here’s the easy one using modulus:

I can’t figure out how to do code tags 😛

See, I had to do 20 previews to try codeblock tags and George then beat me to it 😛

Here is one of my solutions using the SIGN function

and using CASE

CASE can be changed to IIF on SQL Server 2012

Here is a Bitwise AND version as well

After reading: http://www.sqlservercentral.com/blogs/sqlstudies/2013/05/28/using-cross-apply-to-calculate-an-aggregate-across-a-single-row/

I wrote it just for fun as:

SELECT #Puzzle.*, 5 – SUM(remainder) AS EVEN

from #Puzzle

CROSS APPLY (VALUES

(Col1 % 2),

(Col2 % 2),

(Col3 % 2),

(Col4 % 2),

(Col5 % 2) ) Even (remainder)

GROUP BY Col1, Col2, Col3, Col4, Col5

SELECT Col1, Col2, Col3, Col4, Col5, Even = (Col1+1) % 2 + (Col2+1) % 2 + (Col3+1) % 2 + (Col4+1) % 2 + (Col5+1) % 2

FROM #Puzzle;

Which is the same solution as Eli (I didn’t look at the comments before I tried the puzzle).

SELECT Col1,Col2,Col3,Col4,Col5,

(~Col1%2 + ~Col2%2 + ~Col3%2 + ~Col4%2 + ~Col5%2 )*-1 as EVENCOUNT

FROM #Puzzle

The Solution is: Use (Modulo) %2 which gives remainder after dividing Value by 2 : So for odd numbers it will be 1 and even numbers it will be 0 .. we want to count the even numbers so use Tilde ~ to get a Binary NOT of the value and apply Mod.. This will give -1 for all even numbers and 0 for all odd numbers.. sum all this and multiply result by -1 to get final count of even numbers !!

There is a lot of debate whether zero is odd or even .. but many sources agree zero is even number. This solution will also count zero as an even number.

SELECT Col1, Col2, Col3, Col4, Col5,

EvenCount = SUM(CASE WHEN Col1%2=0 THEN 1 ELSE 0 END) +

SUM(CASE WHEN Col2%2=0 THEN 1 ELSE 0 END) +

SUM(CASE WHEN Col3%2=0 THEN 1 ELSE 0 END) +

SUM(CASE WHEN Col4%2=0 THEN 1 ELSE 0 END) +

SUM(CASE WHEN Col5%2=0 THEN 1 ELSE 0 END)

from #Puzzle

GROUP BY Col1, Col2, Col3, Col4, Col5;

How about a solution that doesn’t use a CASE statement, you think you can do it

select Col1

,Col2

,Col3

,Col4

,Col5

,abs((Col1 % 2) – 1) + abs((Col2 % 2) – 1) + abs((Col3 % 2) – 1) + abs((Col4 % 2) – 1)+ abs((Col5 % 2) – 1) EVEN

FROM #PUZZLE