dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Sat Sep 06, 2008 3:48 am Post subject: |
|
|
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 |
|
 |
tcurrier Intermediate
Joined: 10 Feb 2006 Posts: 188 Topics: 68
|
Posted: Sat Sep 06, 2008 6:45 am Post subject: |
|
|
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 |
|
 |
dbzTHEdinosauer Supermod
Joined: 20 Oct 2006 Posts: 1411 Topics: 26 Location: germany
|
Posted: Sat Sep 06, 2008 7:15 am Post subject: |
|
|
your solution is a keeper. thanks. _________________ Dick Brenholtz
American living in Varel, Germany |
|
Back to top |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
|
Posted: Sat Sep 06, 2008 11:11 am Post subject: |
|
|
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 |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
|
Posted: Sat Sep 06, 2008 11:15 am Post subject: |
|
|
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 |
|
 |
tcurrier Intermediate
Joined: 10 Feb 2006 Posts: 188 Topics: 68
|
Posted: Sat Sep 06, 2008 6:49 pm Post subject: |
|
|
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 |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
|
Posted: Sat Sep 06, 2008 8:43 pm Post subject: |
|
|
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 |
|
 |
tcurrier Intermediate
Joined: 10 Feb 2006 Posts: 188 Topics: 68
|
Posted: Sun Sep 07, 2008 7:07 am Post subject: |
|
|
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 |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
|
Posted: Sun Sep 07, 2008 10:15 am Post subject: |
|
|
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 |
|
 |
kolusu Site Admin

Joined: 26 Nov 2002 Posts: 12380 Topics: 75 Location: San Jose
|
Posted: Tue Sep 09, 2008 7:10 pm Post subject: |
|
|
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 |
|
 |
tcurrier Intermediate
Joined: 10 Feb 2006 Posts: 188 Topics: 68
|
Posted: Wed Sep 10, 2008 9:28 am Post subject: |
|
|
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 |
|
 |
NASCAR9 Intermediate
Joined: 08 Oct 2004 Posts: 274 Topics: 52 Location: California
|
Posted: Wed Sep 10, 2008 1:17 pm Post subject: |
|
|
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 |
|
 |
|
|
|
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
|
|