If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > DB2 > db2 count age help

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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 01:27.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,099
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
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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...
Reply With Quote
  #4 (permalink)  
Old
:-)
 
Join Date: Jun 2003
Location: Toronto, Canada
Posts: 5,333
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Nov 2012
Posts: 5
if you do year(current date) - year(birthdate)... you will not get the correct age
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 4,099
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
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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...
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
Join Date: Nov 2011
Posts: 310
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;
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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!
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On