| View previous topic :: View next topic |
| Author |
Message |
sanjayr321
Beginner
Joined: 25 Sep 2006
Posts: 28
Topics: 15
|
 Posted: Tue Aug 28, 2007 12:05 am Post subject: Total number of records |
 |
|
Hai All,
I need to find out the total number of records in a particular table, i.e I want to figure out the percentage of a group of employee..
for ex: consider EMPL table having total 10000 records and out of 10000 records 4000 are with EMPCODE = 'ACCT', if I know the numbers I will findout the percentage (4000/10000) x 100..
Is there any other way to find out the total number of records in a table other than selecting all the records, since it may have millions of records it will take long time to run...
Hope you understand my queries, pls give some suggestions, I looked at CARDF in SYSIBM tables, i can't find out from it..
Thanks.. |
|
| Back to top |
|
 |
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Aug 28, 2007 12:33 am Post subject: |
|
|
sanjayr321,
If you don't want to do a COUNT(*), then insure that the RUNSTATS are current, and look in the sysibm table for the count. The appendix of any SQL guide or reference will tell you in which sys table to look, as well as sample SQL for a query.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
sanjayr321
Beginner
Joined: 25 Sep 2006
Posts: 28
Topics: 15
|
| Posted: Tue Aug 28, 2007 2:31 am Post subject: |
|
|
Hi,
sorry... i can't make sure that RUNSTATS are updating properly ( do not hav access for it) more ever CARDF in float format, how can I identify the number of records in the table from it.
Is there any other way....
Thanks.. |
|
| Back to top |
|
|
dbzTHEdinosauer
Supermod
Joined: 20 Oct 2006
Posts: 1411
Topics: 26
Location: germany
|
| Posted: Tue Aug 28, 2007 2:56 am Post subject: |
|
|
The sql manual will show you how to convert float to other datatypes.
_________________
Dick Brenholtz
American living in Varel, Germany |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Aug 28, 2007 7:30 am Post subject: |
|
|
sanjayr321,
Try this
| Code: |
SELECT (SUM(CASE WHEN EMPCODE = 'ACCT'
THEN DECIMAL((1),13,4)
ELSE DECIMAL((0),13,4)
END) / COUNT(*)) * 100
FROM TABLE
;
|
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
Sarangadhar
Beginner
Joined: 14 Oct 2004
Posts: 130
Topics: 43
Location: virtual village
|
| Posted: Mon Sep 17, 2007 5:43 pm Post subject: |
|
|
Kolusu,
Its working..
What is the forlumal behind decimal ((1),13,4)?
_________________
Thanks |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Sep 18, 2007 7:21 am Post subject: |
|
|
Sarangadhar,
| Code: |
decimal ((1),13,4)? = a value of 1 which should reprensted as 13 (9 whole numbers + 4 decimals)
|
ie.
The reason for using decimals is OP wanted a percentage. Count(*) returns a integer number without any decimals.
for ex you have a table of 40 rows and out of 5 rows has a string 'mvsforums'
Now if you want find the percentage of rows with 'mvsforums' then
% = (5/40) * 100 = 12.5%
In Db2 5/40 yields 0 since there integer divided by integer results in a integer and you don't have any decimals.
But if you use 5.0000/40 it will yield .12500000000000 which is what OP needs
Hope this helps...
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
