View previous topic :: View next topic
|
Author |
Message |
rohitnargunde
New User
Joined: 24 Jun 2009 Posts: 9 Location: India
|
|
|
|
I need to write a query to get count for multiple criterias within same column. Eg -
Column A
900001
900002
900300
905000
990001
990005
..
..
999999
I need to collect information in following format -
Col A having first 3 digits as 900 Count is 10
Col A having first 3 digits as 901 Count is 100
Col A having first 3 digits as 902 Count is 15
...
...
Col A having first 3 digits as 999 Count is 15
Can I do this using single query. I need to run the using DB2 Sql.
Thanks and Regards, Rohit |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
look at the case statement; will do what you want to do.
there are also examples in the forum - there are examples from today's posts.
you did not bother to tell us the data type of column a,
so no one can give you a 'real' solution. |
|
Back to top |
|
|
rohitnargunde
New User
Joined: 24 Jun 2009 Posts: 9 Location: India
|
|
|
|
Thanks for the reply. I tried using Case but I wasn't successful. Data type of Column in Alphanumeric. |
|
Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006 Posts: 19244 Location: Inside the Matrix
|
|
|
|
Hello,
If you actually want help, you need to post what syntax you tried and the error from that try. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
Quote: |
Data type of Column in Alphanumeric. |
part of your problem is that you do not know DB2 data types.
no such thing as a db2 data type of alphanumeric.
it is either char or one of the numeric types.
if char, use substr
if numeric, cast to char and then substr. |
|
Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009 Posts: 1281 Location: Belgium
|
|
|
|
what's wrong with : group by substr(colA,1,3) ? |
|
Back to top |
|
|
rohitnargunde
New User
Joined: 24 Jun 2009 Posts: 9 Location: India
|
|
|
|
i am sorry.. but i do know DB2 data types... i had mentioned alphanumeric because that's what is been used in the DCLGEN copybook..
I had joined the forum to enhance my knowledge and get out of woods when in need. I feel bad that experts on the topic make insensitive statements.
getting back to the topic. Can anyone send me some samples.. if they have handy. |
|
Back to top |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006 Posts: 6966 Location: porcelain throne
|
|
|
|
There is a beginners website associated with this forum,
exactly for people who have no interest in researching or reading the manuals.
and believe me, no-one in this thread was insensitive - except you.
no-one is paid for their contributions,
unlike you who is paid to accomplish a task
for which you seem to think that members of this board should provide solutions.
link for v7 sql reference
vsn 8 Application programmers guide
Redbook: DB2 for z/os Application Programming
There are samples in the manuals.
if cobol is your programming language alphanumeric (x-type) is the equivalent of Character (char)
If it is indeed Char, a combination of CASE substr would work.
but actually, GuyC made an excellent suggestion:
GROUP BY SUBSTR(col,1,3)
why did you not try that?
There would have been no need to invest time in learning CASE,
but unfortunately, you need to understand SUBSTR -uhmm, actually not;
GuyC gave you the answer.
If you wait long enough, probably someone will provide a complete solution,
free of charge,
which you can then submit as your own work,
amaze your co-workers
(with whom you apparently do not communicate)
- and collect your paycheck. |
|
Back to top |
|
|
|