CODEWITHSUNDEEP
sqlt-sql
Zaur
Zaur

Reputation: 123

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

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!

Upvotes: 1

Views: 826

Answers (3)

dnoeth
dnoeth

Reputation: 60472

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

Upvotes: 2

Elbek
Elbek

Reputation: 646

For each row you could count number of odd or even numbers by converting your conditions to int and adding them:

SELECT ( cast( (nr1 % 2==0) as int)+ cast( (nr2 % 2==0) as int) + ....) as even,
( cast( (nr1 % 2!=0) as int)+ cast( (nr2 % 2!=0) as int) + ....) as odd FROM table;

After you get all even and odd counts, you can calculate all results you asked.

Upvotes: 1

GMB
GMB

Reputation: 222512

You can use a lateral join to unpivot the rows and count the number of even and odd values per row.

select 
    sum(cnt_even) total_even_values,
    sum(cnt_odd)  total_odd_values,
    sum(case when cnt_even = 6 then 1 else 0 end) cnt_rows_with_6_even_numbers,
    sum(case when cnt_even = 5 then 1 else 0 end) cnt_rows_with_5_even_numbers,
    sum(case when cnt_even = 4 then 1 else 0 end) cnt_rows_with_4_even_numbers,
    ...
    sum(case when cnt_even = 0 then 1 else 0 end) cnt_rows_with_0_even_numbers
from mytable
cross apply (
    select sum(1 - is_odd) as cnt_even, sum(is_odd) as cnt_odd
    from (values (nr1 % 2), (nr2 % 2), (nr3 % 2), (nr4 % 2), (nr5 % 2), (nr6 % 2)) as x(is_odd)
) x

Upvotes: 2

Related Questions