| View previous topic :: View next topic |
| Author |
Message |
MikeBaker
Beginner
Joined: 04 May 2004
Posts: 96
Topics: 9
|
 Posted: Wed Mar 30, 2005 11:55 pm Post subject: SPUFI for doing a Unique sub-string Select, and a Count |
 |
|
Hi,
If I have the following data:-
| Code: |
ABBBBBBB
ABBBBBBB
ABEEEEEE
ACCCCCCC
ADDDDDDD
AEEEEEEE
AEEEEEEE
AEFFFFFF
AGGGGGGG
|
Then I would like to know the SPUFI for selecting every single Unique occurrence and the Total number of occurrences.
So... for the above data, my results would look like:-
| Code: |
AB = 3
AC = 1
AD = 1
AE = 3
AG = 1
|
For argument's sake, let's pretend the field name is called "SURNAME".
Many thanks. |
|
| Back to top |
|
 |
s_shivaraj
Beginner
Joined: 21 Sep 2004
Posts: 140
Topics: 14
Location: Chennai, India
|
| Posted: Thu Mar 31, 2005 2:08 am Post subject: |
|
|
Hi Mike,
Try this
SELECT count(*), left(surname,2) FROM id.tablename group by left(surname,2);
I tried in windows UDB 8.1, but i dont know whether it works in mainframe Db2, please try this and let us know..
_________________
Cheers
Sivaraj S
'Technical Skill is the Master of complexity, while Creativity is the Master of Simplicity' |
|
| Back to top |
|
|
bauer
Intermediate
Joined: 10 Oct 2003
Posts: 315
Topics: 49
Location: Germany
|
| Posted: Thu Mar 31, 2005 5:39 am Post subject: |
|
|
Hi all,
"group by" with a column function is not allowed (DB2 / OS390, Version 7.2). May be in a newer version ......
So try this:
| Code: |
SELECT COUNT(*), X
FROM (
SELECT LEFT(surname,2) X
FROM id.tablename
) AS Y
GROUP BY X
|
regards,
bauer |
|
| Back to top |
|
|
kolusu
Site Admin
Joined: 26 Nov 2002
Posts: 12376
Topics: 75
Location: San Jose
|
| Posted: Thu Mar 31, 2005 6:40 am Post subject: |
|
|
MikeBaker,
To be precise , try this
| Code: |
SELECT A.SNAME
,CHAR('=')
,COUNT(*) TOT_CNT
FROM(SELECT SUBSTR(SURNAME,1,2) SNAME
FROM H1)A
GROUP BY A.SNAME
;
|
Hope this helps..
Cheers
Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu |
|
| Back to top |
|
|
devjfl1
Beginner
Joined: 03 Jan 2003
Posts: 4
Topics: 1
Location: California
|
| Posted: Wed Apr 13, 2005 4:08 pm Post subject: |
|
|
Kolusu,
I was in need of the same type of query. The SEARCH of your board is very fast and your example worked like a charm.
thanks! |
|
| Back to top |
|
|
|
|
|
FAQ
Search
Quick Manuals
Register
Profile
Log in to check your private messages
Log in
