Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 59
Location: Atlanta

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: 59
Location: Atlanta

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: 59
Location: Atlanta

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: 59
Location: Atlanta

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: 6966
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: 6966
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 Join in SQL Query vickey_dw DB2 2 Tue Jan 17, 2017 12:15 am
No new posts Storing huge volume of data, compare ... Pradeep K M All Other Mainframe Topics 3 Mon Jan 16, 2017 5:08 pm
No new posts How to Capture the Max return code of... anilkumar922 All Other Mainframe Topics 0 Thu Jan 12, 2017 12:02 pm
No new posts how to recover an uncataloged VSAM da... archanamuthukrishnan All Other Mainframe Topics 3 Wed Jan 11, 2017 6:18 pm
This topic is locked: you cannot edit posts or make replies. MQ setup query mohitsaini Java & MQSeries 4 Sun Jan 08, 2017 1:53 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us