Results 1 to 11 of 11
  1. #1
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,378

    Unanswered: Query - Count drivers grouped by year and month

    db2 10 on z/os

    I have a table with all driver license information. I need to get a count of drivers who were 16 years old or older grouped by year and month since January 2007. current date - birthday (column in the table) will give me their current age and I can use it to count the number of drivers who are 16+ as of now. But I'm not sure how to do it for every year and month since Jan 2007. Is it possible to do with sql?

    Thanks

  2. #2
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    yes, something like:

    Code:
    select count(substr(current date - birthdate,1,2)), month(birthdate), year(birthdate)
       from your_table
    where substr(current date - birthdate,1,2) >= 16
    group by month(birthdate), year(birthdate)
    Last edited by DNance; 12-09-16 at 11:59.

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,378
    Quote Originally Posted by DNance View Post
    yes, something like:

    Code:
    select count(substr(current date - birthdate,1,2)), month(birthdate), year(birthdate)
       from your_table
    where substr(current date - birthdate,1,2) >= 16
    group by month(birthdate), year(birthdate)

    This will count the number of drivers based on their current age.

    What I actually need is to get the # of drivers who were 16+ in Jan 2007, Feb 2007, Mar 2007 and so on... till Dec, 2016. Let's say based on their age on the 1st day of every month. Something similar to:

    Code:
    Month	Year	# of drivers with age >= 16
    Jan 1 	2007	25662245
    Feb 1	2007	33453245
    Mar 1	2007 	45599999
    ....
    Dec 1   2016	99793434

    Is this possible?


    Thanks

  4. #4
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Sure, it's possible.
    You can construct a temporary table with the following contents using RCTE, for example.
    DT
    ---
    01.01.2007
    01.02.2007
    ...
    01.12.2016

    Then you can use it like this:

    select t.dt, count(1) cnt
    From t
    Join drivers d on d.birthdate <= t.dt - 16 years
    Group by t.dt
    Regards,
    Mark.

  5. #5
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    or an additional predicate for the date range you want on what I gave you

  6. #6
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,378
    Quote Originally Posted by mark.bb View Post
    Sure, it's possible.
    You can construct a temporary table with the following contents using RCTE, for example.
    DT
    ---
    01.01.2007
    01.02.2007
    ...
    01.12.2016

    Then you can use it like this:

    select t.dt, count(1) cnt
    From t
    Join drivers d on d.birthdate <= t.dt - 16 years
    Group by t.dt

    Could you please give me an example of how to create this temporary table using RCTE?

    Thanks

  7. #7
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,378
    Quote Originally Posted by DNance View Post
    or an additional predicate for the date range you want on what I gave you
    Could you please help with this additional predicate?

    Thanks

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,378
    Sorry guys... I've never had to write SQL beyond simple queries to access the catalog.

  9. #9
    Join Date
    Jul 2016
    Location
    Moscow
    Posts
    294
    Provided Answers: 45
    Quote Originally Posted by db2girl View Post
    Could you please give me an example of how to create this temporary table using RCTE?
    Code:
    with t(dt) as (
    select date('2007-01-01') from sysibm.sysdummy1
      union all
    select dt + 1 month from t where dt < date('2016-12-01')
    )
    select dt
    from t
    order by dt
    Regards,
    Mark.

  10. #10
    Join Date
    Oct 2007
    Posts
    161
    Provided Answers: 9
    Code:
    select count(substr(current date - birthdate,1,2))
           , month(birthdate)
           , year(birthdate)
       from your_table
    where substr(current date - birthdate,1,2) >= 16
    and date_column_you_want >= '01/01/2007'
    group by month(birthdate), year(birthdate)

  11. #11
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,378
    Thanks a lot for your 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
  •