ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Increment Group Counter with CASE

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Increment Group Counter with CASE

    Hi,

    My SQL statement currently reads through customers' birth dates and figures out how old they are. As a test, I'm currently just grouping them in a group 1 through 5. This works... my program indicates the age of each customer and determines which age grouping they're in. But I actually don't need the detail... I would just like to know how many customers are in each age grouping. So I need to increment a counter for each age group, and this is what I don't know how to do. I would like my result to be a summary and look something like this:
    Group 1 768
    Group 2 4252
    Group 3 7965
    Group 4 6815
    Group 5 4678
    So this is just a hypothetical result showing how many customers are in each age group. As a bonus, it would be nice to know the percentage each of those counts made up of the total, but I'm sure that would be harder.

    Here is my SQL statement currently that lists every account and their age and age group:

    Code:
    with bdays as (
      select Acct, int((Current_Date - date(trim(char(CC)) concat trim(char(YY)) concat '-' concat trim(char(MM)) concat '-' concat trim(char(DD))))/10000) as Age
      from MyCusts)
    select Acct, Age, case
      when Age between 18 and 25 then 'Group 1'
      when Age between 26 and 35 then 'Group 2'
      when Age between 36 and 50 then 'Group 3'
      when Age between 51 and 65 then 'Group 4'
      when Age >= 66 then 'Group 5'
      end as Group
    from bdays
    So, how to see how many customers are in each group and just present that summary? Your help would be appreciated.

  • #2
    Just convert your final SELECT into a CTE and Group in the final SELECt the result from the second CTE.
    Something like this:
    Code:
    with bdays as (
      select Acct, int((Current_Date - date(trim(char(CC)) concat trim(char(YY)) concat '-' concat trim(char(MM)) concat '-' concat trim(char(DD))))/10000) as Age
      from MyCusts),
      Y as (select Acct, Age, case when Age between 18 and 25 then 'Group 1'
                                   when Age between 26 and 35 then 'Group 2'
                                   when Age between 36 and 50 then 'Group 3'
                                   when Age between 51 and 65 then 'Group 4'
                                   when Age >= 66 then 'Group 5'
                              end as YourGroup
               from bdays)
    Select Acct, YourGroup, Count(*)
       from y
       Group By Acct, YourGroup;
    Birgitta

    Comment


    • #3
      Thanks! In the last select, I'm just selecting YourGroup and Count(*), and just grouping by YourGroup, and that gives me the results I want.

      Btw, I noticed that I had some issues with concatenating the individual CC, YY, MM, and DD fields where they resulted in invalid dates (due to bad data in those field in some records)... that made the date function fail. How do you normally handle that in a case like with this SQL statement?

      Comment


      • #4
        Originally posted by Viking View Post
        Btw, I noticed that I had some issues with concatenating the individual CC, YY, MM, and DD fields where they resulted in invalid dates (due to bad data in those field in some records)... that made the date function fail. How do you normally handle that in a case like with this SQL statement?
        I'd write a UDF where I pass the data which also handles any SQL errors, here an example UDF for converting a (simple) numeric date, with error handling (if any error occurs 12/31/8888 is returned.
        Code:
        CREATE OR REPLACE FUNCTION HSCOMMON10/NUM2DATE (DATENUM DECIMAL(8, 0) )
                  RETURNS DATE   
               LANGUAGE SQL
               NOT DETERMINISTIC
               READS SQL DATA
               CALLED ON NULL INPUT
        
        BEGIN
           DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN DATE ( '8888-12-31' ) ;
           RETURN DATE ( DIGITS ( DATENUM ) CONCAT '000000' ) ;
        END  ;
        Birgitta

        Comment


        • #5
          Birgitta, I would have thought that this function should be DETERMINISTIC...? Shouldn't it always return the same result for the same input value? Or have I misunderstood this?

          Anyway, I tried this with both DETERMINISTIC and NOT DETERMINISTIC, but the error handling doesn't seem to work for me...

          This correctly gives me the answer 2019-02-28:

          Code:
          select num2date(20190228) from sysibm.sysdummy1
          This gives me an error "Query cannot be run. See lower level mesages.", rather than the 8888-12-31 that I expected:

          Code:
          select num2date(20190229) from sysibm.sysdummy1
          The message in the joblog is "Value in date, time, or timestamp string not valid."

          What am I doing wrong?

          Thanks.
          Last edited by Viking; May 9, 2019, 01:16 PM.

          Comment


          • #6
            Curious - I get the 8888-12-31. Is your date format set to *MDY or *JOB? Make sure it's *ISO

            Comment


            • #7
              I suggest adding a SET OPTION statement:

              Code:
              CREATE OR REPLACE FUNCTION HSCOMMON10/NUM2DATE (DATENUM DECIMAL(8,  0) )                                                             
                        RETURNS DATE                                           
                     LANGUAGE SQL                                              
                     NOT DETERMINISTIC                                         
                     READS SQL DATA                                            
                     CALLED ON NULL INPUT                                      
                    set option DATFMT=*ISO                                           
              BEGIN                                                            
                 DECLARE CONTINUE HANDLER FOR SQLEXCEPTION RETURN DATE (       
              '8888-12-31' ) ;                                                 
                 RETURN DATE ( DIGITS ( DATENUM ) CONCAT '000000' ) ;          
              END

              Comment


              • #8
                Aaargh.. you're right. I just added the set option and now it works. I got bit by this already recently on a different thread and I was set straight that time too... I guess I didn't learn my lesson that last time!

                Thanks.

                p.s. What do you think about DETERMINISTIC vs NOT DETERMINISTIC in this case?

                Comment


                • #9
                  I think Viking is on to something. It looks deterministic to me too.

                  Comment

                  Working...
                  X