Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
1 query to return all count of data for multiple ranges

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 60
Location: San Diego

PostPosted: Tue Dec 16, 2008 3:15 pm    Post subject: 1 query to return all count of data for multiple ranges
Reply with quote

i need to write a single query to fetch count of data in a table , the data is in the form of ranges

something like

beginning range ending table
col1 col1 tab1
1 10 4
100 199 100
etc....

for this i would be writing multiple queries for each range
like
select col1,count (*) from tab1
where itm_seq_no between 1 and 10; and
select col1,count (*) from tab1
where itm_seq_no between 100 and 199;

but i need just 1 query to finish of things
Back to top
View user's profile Send private message

rakesh17684

New User


Joined: 08 Oct 2006
Posts: 60
Location: San Diego

PostPosted: Tue Dec 16, 2008 3:20 pm    Post subject: Re: 1 query to return all count of data for multiple ranges
Reply with quote

few corrections in my above post:

i need to write a single query to fetch count of data in a table , the data is in the form of ranges

something like

beginning range ending table
col1 col1 tab1
1 10 4
100 199 100
etc....

for this i would be writing multiple queries for each range
like
select count (*) from tab1
where itm_seq_no between 1 and 10; and
select count (*) from tab1
where itm_seq_no between 100 and 199;

but i need just 1 query to finish of things
Back to top
View user's profile Send private message
atosvv

New User


Joined: 04 Dec 2008
Posts: 11
Location: Mumbai

PostPosted: Tue Dec 16, 2008 3:50 pm    Post subject: Reply to: 1 query to return all count of data for multiple r
Reply with quote

Hi Rakesh,

Please check the below query fulfill your requirement or not.

Code:

select count(*) as reccount from tab1 where (itm_seq_no between 1 and 5) or (itm_seq_no between 101 and 105);


Thanks.

-VV
Back to top
View user's profile Send private message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 60
Location: San Diego

PostPosted: Tue Dec 16, 2008 4:01 pm    Post subject:
Reply with quote

thanks atosvv

but i need the count for each separate range ,

select count(*) as reccount from tab1 where (itm_seq_no between 1 and 5) or (itm_seq_no between 101 and 105);

will give me the over all count for both these ranges
Back to top
View user's profile Send private message
atosvv

New User


Joined: 04 Dec 2008
Posts: 11
Location: Mumbai

PostPosted: Tue Dec 16, 2008 4:13 pm    Post subject:
Reply with quote

Hi Rakesh,

Yes the above query gives the overall count.

Please try this
Code:

select count(*)  from tab1 where itm_seq_no  between 1 and 10 union select count(*)  from tab1 where itm_seq_no  between 101 and 105;


It gives the count of individual range.

Thanks.

-VV
Back to top
View user's profile Send private message
rakesh17684

New User


Joined: 08 Oct 2006
Posts: 60
Location: San Diego

PostPosted: Tue Dec 16, 2008 4:20 pm    Post subject:
Reply with quote

but still
select count(*) from tab1 where itm_seq_no between 1 and 10 union select count(*) from tab1 where itm_seq_no between 101 and 105;
will make 2 SQL calls but i need to wrap it off with one call.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Tue Dec 16, 2008 4:29 pm    Post subject:
Reply with quote

use CASE to resolve ranges

Code:

SELECT SUM(CASE WHEN ITM_SEQ_NO BETWEEN 1 AND 9
                THEN 1 ELSE 0 END) AS FROM1TO9
      ,SUM(CASE WHEN ITM_SEQ_NO BETWEEN 10 AND 20
                THEN 1 ELSE 0 END) AS FROM10TO20
      ,SUM(CASE WHEN ITM_SEQ_NO BETWEEN 21 AND 30
                THEN 1 ELSE 0 END) AS FROM21TO30
      ,SUM(CASE WHEN ITM_SEQ_NO > 30
                THEN 1 ELSE 0 END) AS OVER30
FROM <your-table>
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6970
Location: porcelain throne

PostPosted: Tue Dec 16, 2008 4:46 pm    Post subject:
Reply with quote

or, something that Kolusu provided as a solution:

Code:

SELECT CHAR('BETWEEN ') ||
       DIGITS(((ITM_SEQ_NO / 10) * 10) + 00) ||
       CHAR(' AND ') ||
       DIGITS(((ITM_SEQ_NO / 10) * 10) + 9)
      ,DIGITS(COUNT(*)) AS COUNT
 FROM <your table>
 GROUP BY
       CHAR('BETWEEN ') ||
       DIGITS(((ITM_SEQ_NO / 10) * 10) + 00) ||
       CHAR(' AND ') ||
       DIGITS(((ITM_SEQ_NO / 10) * 10) + 9)
 ORDER BY 1;


the above will give you ranges for every 10.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Extract record for change in combinat... Trinadh DFSORT/ICETOOL 5 Thu Nov 23, 2017 3:32 pm
No new posts How to query MAXGENS of an existing P... Chuchulo Hatyzak JCL & VSAM 0 Wed Nov 22, 2017 10:23 pm
No new posts Native SQL Query kishpra DB2 1 Wed Nov 22, 2017 8:38 pm
No new posts Fetching data from JHS as per the cur... arunsoods All Other Mainframe Topics 4 Wed Nov 22, 2017 1:54 pm
No new posts Extract Record using range of Data scorp_rahul23 DFSORT/ICETOOL 3 Wed Nov 15, 2017 11:54 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us