15,791,934 members
See more:
I have a table with 6 columns containing random numbers from 1 to 90.

```nr1 nr2 nr3 nr4 nr5 nr6
-----------------------
12  26  70  74  84  87
6   13  19  37  67  72
29  31  35  49  69  74
3   14  30  50  66  87
6   10  13  17  22  46
28  29  33  35  65  80
25  31  43  61  63  86
12  20  22  39  55  72
9   12  28  71  82  85
5   13  28  30  42  63
33  37  48  65  83  84
3   10  40  54  69  85
6   19  30  53  55  76
17  41  42  43  66  76
2   22  28  39  61  79
26  37  53  81  86  90
2   51  55  57  61  82
1   18  30  34  65  75
18  28  40  63  68  86```

I need to produce a query that will provide the following result:
```Total ODD numbers in a table:
Total EVEN numbers in a table:
Total number of rows containing 6 even numbers:
Total number of rows containing 6 odd numbers:
Total number of rows containing 5 even and 1 odd number:
Total number of rows containing 5 odd and 1 even number:
Total number of rows containing 4 even and 2 odd numbers:
Total number of rows containing 4 odd and 2 even numbers:
Total number of rows containing 3 even and 3 odd numbers:```

Any ideas? Thank you!

What I have tried:

So far I could figure out only how to calculate number of rows containing only even or odd numbers, but I'm stuck on the rest.

SQL
```SELECT COUNT(*) AS ROWS_ODDS FROM table
WHERE nr1 %2!=0 AND nr2 %2!=0 AND nr3 %2!=0 AND nr4 %2!=0 AND nr5 %2!=0 AND nr6 %2!=0

SELECT COUNT(*) AS ROWS_EVENS FROM table
WHERE nr1 %2=0 AND nr2 %2=0 AND nr3 %2=0 AND nr4 %2=0 AND nr5 %2=0 AND nr6 %2=0```
Posted
Updated 10-Sep-20 23:30pm

## Solution 2

Create a temporary table and fill it with the odds and evens counts: that's pretty trivial, you can just SELECT to get those:
SQL
```SELECT Nr1%2 + Nr2%2 + Nr3%2 + Nr4%2 + Nr5%2 + Nr6%2 AS Odds,
6 - (Nr1%2 + Nr2%2 + Nr3%2 + Nr4%2 + Nr5%2 + Nr6%2)  AS Evens
INTO #Temp
FROM MyTable```
You can then elect what you need from that:
SQL
```--DROP TABLE IF EXISTS #Temp
IF EXISTS(SELECT [name] FROM tempdb.sys.tables WHERE [name] like '#Temp')
BEGIN
DROP TABLE #Temp;
END;
SELECT Nr1%2 + Nr2%2 + Nr3%2 + Nr4%2 + Nr5%2 + Nr6%2 AS Odds,
6 - (Nr1%2 + Nr2%2 + Nr3%2 + Nr4%2 + Nr5%2 + Nr6%2)  AS Evens
INTO #Temp
FROM MyTable
SELECT 'Total Odds  = ' + CAST(SUM(Odds) AS NVARCHAR) FROM #Temp
SELECT 'Total Evens = ' + CAST(SUM(Evens) AS NVARCHAR) FROM #Temp
DROP TABLE #Temp```

The commented out line is for SQL2016 and greater, for lower version use the second version.

I'll leave the other SELECTs to you to work out!

## Solution 1

Total odd numbers in the table:
SQL
```SELECT
SUM((nr1 & 1) + (nr2 & 1) + (nr3 & 1) + (nr4 & 1) + (nr5 & 1) + (nr6 & 1))
FROM
YourTable
;```
Total even numbers in the table:
SQL
```SELECT
SUM((1 - nr1 & 1) + (1 - nr2 & 1) + (1 - nr3 & 1) + (1 - nr4 & 1) + (1 - nr5 & 1) + (1 - nr6 & 1))
FROM
YourTable
;```
Rows containing six even numbers:
SQL
```SELECT
Count(1)
FROM
YourTable
WHERE
nr1 & 1 = 0
And
nr2 & 1 = 0
And
...```
Rows containing six odd numbers:
SQL
```SELECT
Count(1)
FROM
YourTable
WHERE
nr1 & 1 = 1
And
nr2 & 1 = 1
And
...```

Now the slightly more complicated queries:

5 even and 1 odd:
SQL
```SELECT
Count(1)
FROM
YourTable
WHERE
(nr1 & 1) + (nr2 & 1) + (nr3 & 1) + (nr4 & 1) + (nr5 & 1) + (nr6 & 1) = 1
;```
You should be able to work out the others from there. :)

v2
Zaur Bahramov 11-Sep-20 4:15am
Hi Richard! What does nr1 & 1 stand for?
Richard Deeming 11-Sep-20 4:18am
`&` is the bitwise AND operator:
& (Bitwise AND) (Transact-SQL) - SQL Server | Microsoft Docs[^]

All odd numbers will have bit `1` set, so `@x & 1` will return `1`. All even numbers will not have this bit set, so `@x & 1` will return `0`.