|
View previous topic :: View next topic
|
| Author |
Message |
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
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 |
|
 |
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
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 |
|
|
atosvv
New User
Joined: 04 Dec 2008
Posts: 11
Location: Mumbai
|
|
|
|
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 |
|
|
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
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 |
|
|
atosvv
New User
Joined: 04 Dec 2008
Posts: 11
Location: Mumbai
|
|
|
|
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 |
|
|
rakesh17684
New User
Joined: 08 Oct 2006
Posts: 61
Location: San Diego
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
|
|
