How to figure out if the number of records is odd?

dbGuyo · #1 (**permalink**) 08-16-11, 18:57

Hey guys,

I am going to print 'odd' if the number of records of a table is odd.
Looks so simple but I couldn't so far.

Any idea?

JarlH · #2 (**permalink**) 08-17-11, 02:12

select case when mod(count(*),2) = 1 then 'odd' else 'even' end
from yourtablename

dbGuyo · #3 (**permalink**) 08-17-11, 02:38

Thank you for trying this.
But aggregation functions cannot be used in Where clause!

JarlH · #4 (**permalink**) 08-17-11, 02:50

select 'odd' from sometablename having mod(count(*),2) = 1

stolze · #5 (**permalink**) 08-17-11, 04:48

JarlH didn't use a WHERE clause at all. Or do you mean you don't want to have any result (empty table) in case you have an even number of rows?

dbGuyo · #6 (**permalink**) 08-17-11, 04:53

Thanks guys. Both solutions work fine.
At first glance, I didn't notice that you were using 'case'.

JarlH · #7 (**permalink**) 08-17-11, 05:03

Quote:

Originally Posted by dbGuyo

Have you tried it? It doesn't work either.

This is what I get:

Quote:

SQL>create table dbGuyo (col1 int);
SQL>insert into dbGuyo values (1);
SQL>select 'odd' from dbGuyo having mod(count(*),2) = 1;

===
odd

1 row found

SQL>insert into dbGuyo values (2);
SQL>select 'odd' from dbGuyo having mod(count(*),2) = 1;

0 rows found

Isn't that what you want?

r937 · #8 (**permalink**) 08-17-11, 06:07

are you guys sure that MOD() is a valid ANSI function?

JarlH · #9 (**permalink**) 08-17-11, 06:41

Quote:

Originally Posted by r937

are you guys sure that MOD() is a valid ANSI function?

SQL-2008, Feature T441: ABS and MOD functions

<modulus expression> ::=
MOD <left paren> <numeric value expression dividend> <comma>
<numeric value expression divisor><right paren>

r937 · #10 (**permalink**) 08-17-11, 06:55

thanks jarl... just checking

Internet Services

Web Development

Digital Marketing

Consumer Tech