TSQL: find odd and even numbers in each column for all rows

Question

0.00/5 (No votes)

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 10-Sep-20 21:35pm

Zaur Bahramov

Updated 10-Sep-20 22:30pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Answer 1 · 2020-09-10T22:05:00

Start with the simple queries:

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. :)

OriginalGriff · Answer 2 · 2020-09-10T22:30:00

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!

TSQL: find odd and even numbers in each column for all rows

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0