MVSFORUMS.com Forum Index MVSFORUMS.com
A Community of and for MVS Professionals
 
 FAQFAQ   SearchSearch   Quick Manuals   RegisterRegister 
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 

COUNT function on ranges of values ?

 
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database
View previous topic :: View next topic  
Author Message
tcurrier
Intermediate


Joined: 10 Feb 2006
Posts: 188
Topics: 68

PostPosted: Fri Sep 05, 2008 5:41 pm    Post subject: COUNT function on ranges of values ? Reply with quote

I was wondering if it's possible to do a count on a 'range' of values...

To use a simplified example:

I want to know how many people have $0 - $99 in their savings account,
$100 - $199, $200 - $299, etc. - up to a maximum of $999

Thanks for any help with this...
Back to top
View user's profile Send private message
dbzTHEdinosauer
Supermod


Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany

PostPosted: Sat Sep 06, 2008 3:48 am    Post subject: Reply with quote

Something i pulled out of 5TH edition, DB2 Developers Guide, by Craig Mullins.
Code:

SELECT SUM (CASE WHEN SALARY BETWEEN 0 and 50000 then 1 else 0 end) as upto50
      ,SUM (CASE WHEN SALARY BETWEEN 50001 and 75000 then 1 else 0 end) as upto75
      ,SUM (CASE WHEN SALARY BETWEEN 75001 and 100000 then 1 else 0 end) as upto100
      ,SUM (CASE WHEN SALARY > 100000 then 1 else 0 end) as over100
FROM EMPLOYEE

This will return a single row with columns for each category.

Tested this on DB2V9, on my PC. Should be MF compatible

as info, I have excepted (without permission) the following explanation:
Quote:

This SELECT statement efficiently scans through the data and produces a sum for each range that is defined. The multiple CASE
expressions return either a 0 or a 1. The SUM function just adds up the values and you get totals for employees within each range.
This outperforms multiple SELECT statements using COUNT(*) because DB2 can keep running totals as it passes once through the data
using the CASE expressions.
CAUTION
The previous example shows the CASE expression returning either a 0 or a 1. If you did not include the ELSE portion of
each CASE expression, DB2 would return either a 1 or a NULL. This can be more efficient than returning 1 or 0 for very
large sets of data. This is so because DB2 will actually add the zeroes, but it can ignore the NULLs.
However, be careful, because if there are any categories where no rows apply, then DB2 will SUM up a bunch of
NULLs
_________________
Dick Brenholtz
American living in Varel, Germany
Back to top
View user's profile Send private message
tcurrier
Intermediate


Joined: 10 Feb 2006
Posts: 188
Topics: 68

PostPosted: Sat Sep 06, 2008 6:45 am    Post subject: Reply with quote

Thanks... That works great.

I did find an SQL book 'SQL second edition' by Chris Fehily, and it contained the following example :

Code:
SELECT                                               
    CASE                                             
       WHEN (POLICY_ANNUAL_PREM  BETWEEN             
            0 AND 999)                               
            THEN 'BETWEEN  0,000.00 AND  0,999.99'   
       WHEN (POLICY_ANNUAL_PREM  BETWEEN             
            1000 AND 1999)                           
            THEN 'BETWEEN  1,000.00 AND  1,999.99'   
       WHEN (POLICY_ANNUAL_PREM  BETWEEN             
            2000 AND 2999)                           
            THEN 'BETWEEN  2,000.00 AND  2,999.99'   
    END                                               
       AS "ANNUAL PREMIUMS",                         
    COUNT(*) AS "PREMIUM"                             
FROM MYTABLE         
GROUP BY                                             
    CASE                                             
       WHEN (POLICY_ANNUAL_PREM  BETWEEN             
            0 AND 999)                               
            THEN 'BETWEEN  0,000.00 AND  0,999.99'   
       WHEN (POLICY_ANNUAL_PREM  BETWEEN             
            1000 AND 1999)                           
            THEN 'BETWEEN  1,000.00 AND  1,999.99'   
       WHEN (POLICY_ANNUAL_PREM  BETWEEN             
            2000 AND 2999)                           
            THEN 'BETWEEN  2,000.00 AND  2,999.99'   
    END                                             
ORDER BY MIN(POLICY_ANNUAL_PREM) ASC; 


Code:
Result:
       
 ANNUAL PREMIUMS                      COUNT
 -------------------------------  ----------- 
 BETWEEN  0,000.00 AND  0,999.99        89524 
 BETWEEN  1,000.00 AND  1,999.99        80964 
 BETWEEN  2,000.00 AND  2,999.99        18222 


This will work out better for me, as I need to import the output
into an Excel worksheet.
Back to top
View user's profile Send private message
dbzTHEdinosauer
Supermod


Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany

PostPosted: Sat Sep 06, 2008 7:15 am    Post subject: Reply with quote

your solution is a keeper. thanks.
_________________
Dick Brenholtz
American living in Varel, Germany
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose

PostPosted: Sat Sep 06, 2008 11:11 am    Post subject: Reply with quote

tcurrier,

How about this untested sql which gives you entire range without even having to specify a case statement for every range

The logic is simple. Just divide the policy premium amount by the desired range (in this case 100) and we will only take the integer portion of it neglecting the decimals.

so for example we have the following amounts

Code:

50.64
13.89
110.23
2233.45


now
Code:

int(50.64/100)   = 0
int(13.89/100)   = 0
int(110.23/100)  = 1
int(2233.45/100) = 22


Now we use this integer values to get the range count. If we group by these integer values we will have
Code:

0001.00 - 0100.00   2
0101.00 - 0200.00   1
2201.00 - 2300.00   1


By doing this way we dont have to worry about adding more ranges in future. 1 sql to cover all the ranges

Code:

SELECT CHAR('BETWEEN ') ||                                   
       CHAR((INT(POLICY_ANNUAL_PREM / 100) * 100)  + 001)  ||
       CHAR('.00 AND ') ||                                   
       CHAR((INT(POLICY_ANNUAL_PREM / 100) * 100)  + 100)  ||
       CHAR('.00 ')  AS "ANNUAL PREMIUMS"                       
      ,COUNT(*)      AS "COUNT"                                 
  FROM MY TABLE                                             
 GROUP BY                                                     
       CHAR('BETWEEN ') ||                                   
       CHAR((INT(POLICY_ANNUAL_PREM / 100) * 100)  +  1)  || 
       CHAR('.00 AND ') ||                                   
       CHAR((INT(POLICY_ANNUAL_PREM / 100) * 100)  + 100)  ||
       CHAR('.00 ');                                         



Hope this helps

Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose

PostPosted: Sat Sep 06, 2008 11:15 am    Post subject: Reply with quote

tcurrier,

Oh btw the case statement is ignoring the records with end ranges. for 99.nn. for example if you had 1999.92 as the amount you would ignore it as you are only checking till 1,999 and the next range starts from 2000.

1999.01 to 1999.99 records are all ignored and the same is the case with other ranges too.

Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
tcurrier
Intermediate


Joined: 10 Feb 2006
Posts: 188
Topics: 68

PostPosted: Sat Sep 06, 2008 6:49 pm    Post subject: Reply with quote

Thanks....

Below are the results using your version of the query.

What 'ORDER BY' clause would I use to get the annual premiums
listed in 'value' order ? Also, using your example, since you are dealing with integers, can I also include the cents values (as in 1999.01 to 1999.99) in the range check ?

Code:
ANNUAL PREMIUMS                                   COUNT
------------------------------------------  -----------
BETWEEN 1          .00 AND 100        .00           669
BETWEEN 1001       .00 AND 1100       .00         14066
BETWEEN 101        .00 AND 200        .00           845
BETWEEN 1101       .00 AND 1200       .00         12101
BETWEEN 1201       .00 AND 1300       .00         10666
BETWEEN 1301       .00 AND 1400       .00          9223
BETWEEN 1401       .00 AND 1500       .00          8148
BETWEEN 1501       .00 AND 1600       .00          7228
BETWEEN 1601       .00 AND 1700       .00          6153
BETWEEN 1701       .00 AND 1800       .00          5172
BETWEEN 1801       .00 AND 1900       .00          4446
BETWEEN 1901       .00 AND 2000       .00          3761
BETWEEN 2001       .00 AND 2100       .00          3126
BETWEEN 201        .00 AND 300        .00           796
BETWEEN 2101       .00 AND 2200       .00          2699
BETWEEN 2201       .00 AND 2300       .00          2340
BETWEEN 2301       .00 AND 2400       .00          2089
BETWEEN 2401       .00 AND 2500       .00          1857
BETWEEN 2501       .00 AND 2600       .00          1574
BETWEEN 2601       .00 AND 2700       .00          1396
BETWEEN 2701       .00 AND 2800       .00          1173
BETWEEN 2801       .00 AND 2900       .00           979
BETWEEN 2901       .00 AND 3000       .00           989
BETWEEN 3001       .00 AND 3100       .00           762
BETWEEN 301        .00 AND 400        .00          2284
BETWEEN 3101       .00 AND 3200       .00           686
BETWEEN 3201       .00 AND 3300       .00           549
BETWEEN 3301       .00 AND 3400       .00           515
BETWEEN 3401       .00 AND 3500       .00           428
BETWEEN 3501       .00 AND 3600       .00           375
BETWEEN 3601       .00 AND 3700       .00           349
BETWEEN 3701       .00 AND 3800       .00           296
BETWEEN 3801       .00 AND 3900       .00           238
BETWEEN 3901       .00 AND 4000       .00           236
BETWEEN 4001       .00 AND 4100       .00           186
BETWEEN 401        .00 AND 500        .00          6162
BETWEEN 4101       .00 AND 4200       .00           153
BETWEEN 4201       .00 AND 4300       .00           150
BETWEEN 4301       .00 AND 4400       .00           110
BETWEEN 4401       .00 AND 4500       .00           106
BETWEEN 4501       .00 AND 4600       .00           103
BETWEEN 4601       .00 AND 4700       .00            88
BETWEEN 4701       .00 AND 4800       .00            89
BETWEEN 4801       .00 AND 4900       .00            78
BETWEEN 4901       .00 AND 5000       .00            50
BETWEEN 5001       .00 AND 5100       .00            54
BETWEEN 501        .00 AND 600        .00          9539
BETWEEN 5101       .00 AND 5200       .00            42
BETWEEN 5201       .00 AND 5300       .00            36
BETWEEN 5301       .00 AND 5400       .00            34
BETWEEN 5401       .00 AND 5500       .00            30
BETWEEN 5501       .00 AND 5600       .00            32
BETWEEN 5601       .00 AND 5700       .00            25
BETWEEN 5701       .00 AND 5800       .00            14
BETWEEN 5801       .00 AND 5900       .00            18
BETWEEN 5901       .00 AND 6000       .00            15
BETWEEN 6001       .00 AND 6100       .00            14
BETWEEN 601        .00 AND 700        .00         14341
BETWEEN 6101       .00 AND 6200       .00            12
BETWEEN 6201       .00 AND 6300       .00             6
BETWEEN 6301       .00 AND 6400       .00             5
BETWEEN 6401       .00 AND 6500       .00             5
BETWEEN 6501       .00 AND 6600       .00             6
BETWEEN 6601       .00 AND 6700       .00             4
BETWEEN 6701       .00 AND 6800       .00             2
BETWEEN 6801       .00 AND 6900       .00             3
BETWEEN 6901       .00 AND 7000       .00             3
BETWEEN 7001       .00 AND 7100       .00             3
BETWEEN 701        .00 AND 800        .00         18879
BETWEEN 7101       .00 AND 7200       .00             3
BETWEEN 7201       .00 AND 7300       .00             6
BETWEEN 7301       .00 AND 7400       .00             3
BETWEEN 7401       .00 AND 7500       .00             1
BETWEEN 7501       .00 AND 7600       .00             2
BETWEEN 7601       .00 AND 7700       .00             1
BETWEEN 7701       .00 AND 7800       .00             3
BETWEEN 7901       .00 AND 8000       .00             4
BETWEEN 8001       .00 AND 8100       .00             1
BETWEEN 801        .00 AND 900        .00         19158
BETWEEN 8101       .00 AND 8200       .00             2
BETWEEN 8301       .00 AND 8400       .00             1
BETWEEN 901        .00 AND 1000       .00         16851
BETWEEN 9101       .00 AND 9200       .00             1
BETWEEN 9201       .00 AND 9300       .00             1
BETWEEN 9701       .00 AND 9800       .00             1
BETWEEN 9901       .00 AND 10000      .00             1
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose

PostPosted: Sat Sep 06, 2008 8:43 pm    Post subject: Reply with quote

tcurrier wrote:
Thanks....

Below are the results using your version of the query.

What 'ORDER BY' clause would I use to get the annual premiums
listed in 'value' order ? Also, using your example, since you are dealing with integers, can I also include the cents values (as in 1999.01 to 1999.99) in the range check ?


tcurrier,

Use DIGITS function instead of Char function which will have the leading zeroes. Since you are exporting the results to excel I suggest you use DIGITS function on the Count also , so that the count is readable

The 00 and 99 cent is very simple

Here is an untested sql which I think would give you the desired results

Code:

SELECT CHAR('BETWEEN ') ||                                   
       DIGITS((INT(POLICY_ANNUAL_PREM / 100) * 100)  + 00)  ||
       CHAR('.00 AND ') ||                                   
       DIGITS((INT(POLICY_ANNUAL_PREM / 100) * 100)  + 99)  ||
       CHAR('.99 ')     AS ANNUAL_PREMIUM                     
      ,DIGITS(COUNT(*)) AS COUNT                             
  FROM MY TABLE                                             
 GROUP BY                                                     
       CHAR('BETWEEN ') ||                                   
       DIGITS((INT(POLICY_ANNUAL_PREM / 100) * 100)  + 00)  ||
       CHAR('.00 AND ') ||                                   
       DIGITS((INT(POLICY_ANNUAL_PREM / 100) * 100)  + 99)  ||
       CHAR('.99 ')                                           
 ORDER BY 1;                                                 



Hope this helps...

Cheers

Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
tcurrier
Intermediate


Joined: 10 Feb 2006
Posts: 188
Topics: 68

PostPosted: Sun Sep 07, 2008 7:07 am    Post subject: Reply with quote

I changed the grouping from 100's to 1000's, but your code works perfectly:


Code:
ANNUAL                                             
PREMIUM                                   COUNT     
----------------------------------------  ----------
BETWEEN 0000000000.00 AND 0000000999.99   0000089524
BETWEEN 0000001000.00 AND 0000001999.99   0000080964
BETWEEN 0000002000.00 AND 0000002999.99   0000018222
BETWEEN 0000003000.00 AND 0000003999.99   0000004434
BETWEEN 0000004000.00 AND 0000004999.99   0000001113
BETWEEN 0000005000.00 AND 0000005999.99   0000000300
BETWEEN 0000006000.00 AND 0000006999.99   0000000060
BETWEEN 0000007000.00 AND 0000007999.99   0000000026
BETWEEN 0000008000.00 AND 0000008999.99   0000000004
BETWEEN 0000009000.00 AND 0000009999.99   0000000004


Thanks very much for all your help... I know I'm pushing my
luck here, but is there a way to automatically insert the
thousands separator (,) into the counts ? I know I can do this
in Excel, but I have had other queries in the past for which I was
interested in doing this also.
Back to top
View user's profile Send private message
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose

PostPosted: Sun Sep 07, 2008 10:15 am    Post subject: Reply with quote

tcurrier wrote:
but is there a way to automatically insert the
thousands separator (,) into the counts ? I know I can do this
in Excel, but I have had other queries in the past for which I was
interested in doing this also.


Db2 Version 9 has a function called Currency which I think would give you the desired results.

Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
kolusu
Site Admin
Site Admin


Joined: 26 Nov 2002
Posts: 12380
Topics: 75
Location: San Jose

PostPosted: Tue Sep 09, 2008 7:10 pm    Post subject: Reply with quote

tcurrier,

Try this sql and let me know the results

Code:

SELECT CHAR('BETWEEN ')                                          ||
       DIGITS((INT(POLICY_ANNUAL_PREM / 100) * 100)  +  00)      ||
       CHAR('.01 AND ')                                          ||
       DIGITS((INT(POLICY_ANNUAL_PREM / 100) * 100)  +  99)      ||
       CHAR('.99 ')   AS ANNUAL_PREMIUM                             
      ,CHAR(STRIP(REPLACE(                                         
       SUBSTR(DIGITS(INT(COUNT(*)/1000000000)),8,3)              ||
       CHAR(',')                                                 ||
       SUBSTR(DIGITS(INT(MOD(COUNT(*),1000000000)/                 
                             1000000)),8,3)                      ||
       CHAR(',')                                                 ||
       SUBSTR(DIGITS(INT(MOD(MOD(COUNT(*),1000000000),             
                             1000000)/1000)),8,3)                ||
       CHAR(',')                                                 ||
       SUBSTR(DIGITS(MOD(MOD(MOD(COUNT(*),1000000000),             
              1000000),1000)),8,3),'000,',''),L,'0'))               
  FROM My Table
 GROUP BY                                                           
       CHAR('BETWEEN ')                                          ||
       DIGITS((INT(POLICY_ANNUAL_PREM / 100) * 100)  + 00)       ||
       CHAR('.01 AND ')                                          ||
       DIGITS((INT(POLICY_ANNUAL_PREM / 100) * 100)  + 99)       ||
       CHAR('.99 ')                                                 
 ORDER BY 1;                                                       


Kolusu
Back to top
View user's profile Send private message Send e-mail Visit poster's website
tcurrier
Intermediate


Joined: 10 Feb 2006
Posts: 188
Topics: 68

PostPosted: Wed Sep 10, 2008 9:28 am    Post subject: Reply with quote

Other than right justification of the counts, it's looking good... Thanks !

Code:
ANNUAL                                                   
PREMIUM                                   COUNT           
----------------------------------------  ---------------
BETWEEN 0000000000.01 AND 0000000099.99   667           
BETWEEN 0000000100.01 AND 0000000199.99   846           
BETWEEN 0000000200.01 AND 0000000299.99   797           
BETWEEN 0000000300.01 AND 0000000399.99   2,278         
BETWEEN 0000000400.01 AND 0000000499.99   6,163         
BETWEEN 0000000500.01 AND 0000000599.99   9,567         
BETWEEN 0000000600.01 AND 0000000699.99   14,348         
BETWEEN 0000000700.01 AND 0000000799.99   18,883         
BETWEEN 0000000800.01 AND 0000000899.99   19,156         
BETWEEN 0000000900.01 AND 0000000999.99   16,822         
BETWEEN 0000001000.01 AND 0000001099.99   14,057         
BETWEEN 0000001100.01 AND 0000001199.99   12,129         
BETWEEN 0000001200.01 AND 0000001299.99   10,666         
Back to top
View user's profile Send private message
NASCAR9
Intermediate


Joined: 08 Oct 2004
Posts: 274
Topics: 52
Location: California

PostPosted: Wed Sep 10, 2008 1:17 pm    Post subject: Reply with quote

I want say thanks Kolusu! I will put this in my bag of tricks for the future. One change I made was CHAR('.01 AND ') to CHAR('.00 AND ')
The 100.00, 200.00... were being omitted.
_________________
Thanks,
NASCAR9
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic   printer-friendly view    MVSFORUMS.com Forum Index -> Database All times are GMT - 5 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


MVSFORUMS
Powered by phpBB © 2001, 2005 phpBB Group