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: 6967
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: 6967
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 CICS Transaction attach count of supp... lind sh CICS 1 Wed Jun 21, 2017 1:33 pm
No new posts NDM syntax checking without actually ... GAPX1 All Other Mainframe Topics 0 Wed Jun 07, 2017 2:36 am
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am
No new posts Replace values in the input data Vikas Maharnawar DFSORT/ICETOOL 10 Thu May 11, 2017 2:18 pm
No new posts Check if any Detail records and extra... V S Amarendra Reddy SYNCSORT 19 Mon May 08, 2017 8:54 pm


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