| View previous topic :: View next topic |
| Author |
Message |
WallaceC
Beginner
Joined: 17 Dec 2002
Posts: 22
Topics: 10
|
 Posted: Tue Feb 16, 2010 4:12 pm Post subject: Count Occurrences of Each Length within A Field |
 |
|
I have a table TBL with the following fields:
FLD_1 char(5) - key
FLD_2 char(5) - key
FLD_3 char(250) - non-key
Sample Data:
| Code: | FLD_1 FLD_2 FLD_3
----- ----- --------------------------
AAAAA 11111 123
AAAAA 22222 123456
BBBBB 11111 123456
BBBBB 22222 123456789 |
I would like to know the occurrences of every length of data in FLD_3. For example, the result should look like this:
| Code: |
COUNT LENGTH
----- ------
1 3
2 6
1 9
|
Could anyone help on how the query should be? Also, if FLD_3 is a VARCHAR, will the query be different? Thank you. |
|
| Back to top |
|
 |
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Tue Feb 16, 2010 4:37 pm Post subject: |
|
|
WallaceC,
Untested SQL
| Code: |
SELECT COUNT(*) AS COUNT
,A.FLD3_LEN AS LENGTH
FROM (SELECT LENGTH(FLD_3) FLD3_LEN
FROM TABLE) A
GROUP BY A.FLD3_LEN
;
|
|
|
| Back to top |
|
|
WallaceC
Beginner
Joined: 17 Dec 2002
Posts: 22
Topics: 10
|
| Posted: Wed Feb 17, 2010 10:53 am Post subject: |
|
|
Hi Kolusu,
I tried the query but got incorrect result:
| Code: |
Query:
--
SELECT COUNT(*) AS COUNT
,A.FLD_LEN AS LENGTH
FROM (SELECT LENGTH(FLD_3) FLD_LEN
FROM TBL) A
GROUP BY A.FLD_LEN
WITH UR;
---------+---------+---------+---------+---------+---------+---
COUNT LENGTH
---------+---------+---------+---------+---------+---------+---
17 65
DSNE610I NUMBER OF ROWS DISPLAYED IS 1
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
Data on table:
--
FLD_3
---------+---------+---------+---------+---------+---------+----
XXXXXXXXXXXXXXXXXXXXXX
XXXXXXXXXX
XXXXXXXX XXXX
XXXXXX X XXX XXXXXXXXXX
XXXXXX X XXXXX XXX XXXXXX
XXX X XXXXX XXXX
XXX X XXXXXXXXXX XXXX
XXX X XXXXX
XXX X XXXX XXX
XXX X XXXXXXX XXXX
XXXXXX
XXXXXX X XXXXX XXXXXXXXXXXX
XXXXXXXX X XXXXXXX XXXXX XXXXXXXXXX XXXXXXXX XXXXXXX XXXXXXXXXX
XXXXXXXX X XXXXXXXX XXXXXXXXXX
XXXXXX X XXXXXX XXXXXX
XXXXX
XXXXXXXX X XXXXXXX
DSNE610I NUMBER OF ROWS DISPLAYED IS 17
|
[/code] |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12378
Topics: 75
Location: San Jose
|
| Posted: Wed Feb 17, 2010 11:28 am Post subject: |
|
|
WallaceC,
I assumed your Column is defined as VARCHAR which stores the data length. Looks like your column is just a CHAR format. Change the SQL to the following
| Code: |
SELECT COUNT(*) AS COUNT
,A.FLD3_LEN AS LENGTH
FROM (SELECT LENGTH(RTRIM(FLD_3)) FLD3_LEN
FROM TABLE) A
GROUP BY A.FLD3_LEN
;
|
|
|
| Back to top |
|
|
WallaceC
Beginner
Joined: 17 Dec 2002
Posts: 22
Topics: 10
|
| Posted: Wed Feb 17, 2010 12:52 pm Post subject: |
|
|
Hi Kolusu,
Yes, you are correct that the fields is just a CHAR format. I tried the new query and it works perfectly. Thank you. |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
