Results 1 to 10 of 10
  1. #1
    Join Date
    Jul 2011
    Posts
    14

    Red face Unanswered: How to figure out if the number of records is odd?

    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?

  2. #2
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    select case when mod(count(*),2) = 1 then 'odd' else 'even' end
    from yourtablename

  3. #3
    Join Date
    Jul 2011
    Posts
    14
    Thank you for trying this.
    But aggregation functions cannot be used in Where clause!

  4. #4
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    select 'odd' from sometablename having mod(count(*),2) = 1

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Jul 2011
    Posts
    14
    Thanks guys. Both solutions work fine.
    At first glance, I didn't notice that you were using 'case'.
    Last edited by dbGuyo; 08-17-11 at 06:06.

  7. #7
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by dbGuyo View Post
    Have you tried it? It doesn't work either.

    This is what I get:
    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?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    are you guys sure that MOD() is a valid ANSI function?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    Quote Originally Posted by r937 View Post
    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>

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thanks jarl... just checking
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •