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: 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: 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 Data replication from multiple Db2 ta... kishpra DB2 0 Mon Mar 27, 2017 9:58 pm
No new posts Unable to catalog a gdg dataset resid... Shovan JCL & VSAM 7 Fri Mar 24, 2017 2:24 pm
No new posts Count the length of the record & ... sreekusr DFSORT/ICETOOL 4 Thu Mar 23, 2017 7:52 pm
No new posts Export flat file data into excel sheet murali.andaluri DFSORT/ICETOOL 2 Mon Mar 20, 2017 5:39 pm
No new posts Append data from two files into a sin... Praveen04 DFSORT/ICETOOL 5 Thu Mar 16, 2017 7:29 pm


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