Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2012
    Posts
    5

    db2 count age help

    Hi everyone!
    I’m studding DB2 at my university and I have a question,
    I have to write a query to show the empno and current age of each employee in the employee table.
    I did this using

    SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE
    FROM DANDY.EMPLOYEE
    ORDER BY AGE;

    Now I have to modify this query and add a third column that shows a count of how many OTHER employees have an age that is less than or equal to that in the current result row. how can i do this?

    I'm using DB2 Version 9 on a Windows 7 Enterprise 32bit.

    BEGIN ATOMIC
    DECLARE AGE2 INT;
    SET AGE2= YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE;
    SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE, COUNT(*) AS OTHER
    FROM DANDY.EMPLOYEE
    WHEN AGE>AGE2 THEN COUNT(*)
    WHEN AGE=AGE2 THEN COUNT(*)
    ORDER BY AGE;
    END

    i tried using a variable(age2) to store the value of AGE but it doesn't work... i'm not sure if i'm thinking right with this query...

    i got errors

    SET AGE2= YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token ")" was found following "E) - DATE(BIRTHDATE)".
    Expected tokens may include: ")". SQLSTATE=42601

    SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE, COUNT(*) AS OTHER FROM DANDY.EMPLOYEE WHEN AGE>AGE2 THEN COUNT(*) WHEN AGE=AGE2 THEN COUNT(*) ORDER BY AGE
    SQL0104N An unexpected token "EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRT"
    was found following "SELECT ". Expected tokens may include:
    "<case_simple_type>". SQLSTATE=42601

    END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    Thanks for the help,
    stefan
    Last edited by stefan92; 11-29-12 at 02:27.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,139
    What have you tried? As a student, it is best if you learn from your own experience then to rely on someone giving you the answer. Post what you have tried, then ask for advice.

    Andy

  3. #3
    Join Date
    Nov 2012
    Posts
    5
    hi,

    i managed to get the following code working

    SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE, (
    SELECT COUNT(*) FROM
    (SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) FROM DANDY.EMPLOYEE WHERE YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) < 30))
    FROM DANDY.EMPLOYEE;

    but instead of 30 i want to put the age and run through the whole table and compare the age...

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,452
    Quote Originally Posted by stefan92 View Post
    hi,

    SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE, (
    SELECT COUNT(*) FROM
    (SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) FROM DANDY.EMPLOYEE WHERE YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) < 30))
    FROM DANDY.EMPLOYEE;

    but instead of 30 i want to put the age and run through the whole table and compare the age...
    Firstly, "YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE))" does not make much sense: CURRENT DATE (and, I presume, BIRTHDATE) is already a date, so there's no point in casting it to day. Then, subtracting one date from another gives you a duration, which is a decimal number, so applying YEAR() to it won't return what you expect. In my view, a simpler approach would be to subtract the year portion of the birth date from the year portion of the current date.

    You can reference in the subquery columns from the "outer" portion of the query, only you need to assign correlation names to tables so that DB2 understands what you mean, something like:
    Code:
    select 
      t1.empno, 
      t1.birthdate, 
      (select count(1) 
       from employee t2 
       where t2.birthdate > t1.birthdate
      ) younger_count 
    from 
      employee t1
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Nov 2012
    Posts
    5
    if you do year(current date) - year(birthdate)... you will not get the correct age

  6. #6
    Join Date
    Jan 2003
    Posts
    4,139
    Quote Originally Posted by stefan92 View Post
    if you do year(current date) - year(birthdate)... you will not get the correct age
    That is true, but that is not what was posted. I suggest that you look closely before you post. What was posted does work.

    Andy

  7. #7
    Join Date
    Nov 2012
    Posts
    5
    Now i understand, why compare the age when you can compare the birthdate... Sorry for not paying attention, and thank you for the help...

  8. #8
    Join Date
    Nov 2011
    Posts
    317
    Maybe the olap function will help you to do this:
    Code:
    SELECT EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE
                , count(0) over ( order by birthday ) - count(0) over ( partition by birthday ) as age_count
        FROM DANDY.EMPLOYEE;

  9. #9
    Join Date
    Nov 2012
    Posts
    5
    I finished the code and it works perfectly.

    SELECT t1.EMPNO, YEAR(DATE(CURRENT DATE) - DATE(BIRTHDATE)) AS AGE,
    (SELECT COUNT(*) - 1 FROM DANDY.EMPLOYEE t2 WHERE YEAR(DATE(CURRENT DATE) - DATE(t2.BIRTHDATE)) <= YEAR(DATE(CURRENT DATE) - DATE(t1.BIRTHDATE))
    ) AS OTHER
    FROM DANDY.EMPLOYEE t1
    ORDER BY AGE;

    I'm not sure if "YEAR(DATE(CURRENT DATE) - DATE(t1.BIRTHDATE)))" is correct syntax-wise.
    Anyway thanks for the help!

Posting Permissions

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