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

debugcn 投稿 Dev

Rocco

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:

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.

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

Any ideas? Thank you!

dnoeth

Simply sum the modulo results:

with cte as 
 (
   SELECT 
      nr1 %2 + nr2 %2 + nr3 %2 + nr4 %2 + nr5 %2 + nr6 %2 as odd_values
   FROM tab
 )
select odd_values, 6-odd_values as even_values, count(*)
from cte 
group by odd_values
-- this adds the grand total
union all
select
   sum(odd_values), sum(6-odd_values), -1
from cte

Formatting the output is up to you :-)

See fiddle

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]