CODEWITHSUNDEEP
sqloracle-database
Fshamri
Fshamri

Reputation: 1386

rune one query for multiple ranges in BETWEEN clause in oracle

I am using the below sql query in oracle to get the count of rows in a range which works fine

SELECT count(SERIALNR)
  FROM my_tab
 WHERE SERIALNR BETWEEN '93266070760000' AND '93266070809999';

but how the query could be done if i have to run one query for multiple ranges like the below all in one query

93266070560000  93266070609999
93266070610000  93266070659999
93266070660000  93266070709999
93266070710000  93266070759999
93266070760000  93266070809999

NOTE: the Type of SERIALNR is NUMBER

Upvotes: 0

Views: 1224

Answers (6)

Edwin A
Edwin A

Reputation: 1

@thorsten or instead of all the unionall statements, maybe something like this:

SELECT CASE WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN c1
            WHEN serialnr BETWEEN '93266070610000' AND '93266070659999' THEN c2 
            WHEN serialnr BETWEEN '93266070660000' AND '93266070709999' THEN c3 
            WHEN serialnr BETWEEN '93266070710000' AND '93266070759999' THEN c4 
            WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN c5 
            end Category, count(serialnr) total
FROM my_tab 
group by CASE WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN c1
              WHEN serialnr BETWEEN '93266070610000' AND '93266070659999' THEN c2 
              WHEN serialnr BETWEEN '93266070660000' AND '93266070709999' THEN c3 
              WHEN serialnr BETWEEN '93266070710000' AND '93266070759999' THEN c4 
              WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN c5 
              end;

I'm not certain which is more efficient though:

Upvotes: 0

Barbaros Özhan
Barbaros Özhan

Reputation: 65323

You seem needing UNION ALL to get each range seperately

SELECT 'Range 1' AS "Range", count (SERIALNR) AS "Count"
  FROM tab
 WHERE SERIALNR BETWEEN 93266070560000 AND 93266070809999
 UNION ALL 
SELECT 'Range 2', count (SERIALNR)
  FROM tab
 WHERE SERIALNR BETWEEN 93266070610000 AND 93266070659999
 UNION ALL 
SELECT 'Range 3', count (SERIALNR)
  FROM tab
 WHERE SERIALNR BETWEEN 93266070660000 AND 93266070709999
 UNION ALL 
SELECT 'Range 4', count (SERIALNR)
  FROM tab
 WHERE SERIALNR BETWEEN 93266070710000 AND 93266070759999
 UNION ALL 
SELECT 'Range 5', count (SERIALNR)
  FROM tab
 WHERE SERIALNR BETWEEN 93266070760000 AND 93266070809999

and you don't need to quote of SERIALNR as having numeric data type.

Upvotes: 2

Thorsten Kettner
Thorsten Kettner

Reputation: 94939

You are looking for conditional aggregation, if you want the counts in columns:

SELECT 
  COUNT(CASE WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN 1 END) AS c1,
  COUNT(CASE WHEN serialnr BETWEEN '93266070610000' AND '93266070659999' THEN 1 END) AS c2,
  COUNT(CASE WHEN serialnr BETWEEN '93266070660000' AND '93266070709999' THEN 1 END) AS c3,
  COUNT(CASE WHEN serialnr BETWEEN '93266070710000' AND '93266070759999' THEN 1 END) AS c4,
  COUNT(CASE WHEN serialnr BETWEEN '93266070760000' AND '93266070809999' THEN 1 END) AS c5
FROM my_tab 
WHERE serialnr BETWEEN '93266070560000' AND '93266070809999';

Or for UNION ALL, if you want the counts in rows:

WITH ranges AS
(
  SELECT '93266070760000' AS s1, '93266070809999' AS s2 FROM DUAL
  UNION ALL
  SELECT '93266070610000' AS s1, '93266070659999' AS s2 FROM DUAL
  UNION ALL
  SELECT '93266070660000' AS s1, '93266070709999' AS s2 FROM DUAL
  UNION ALL
  SELECT '93266070710000' AS s1, '93266070759999' AS s2 FROM DUAL
  UNION ALL
  SELECT '93266070760000' AS s1, '93266070809999' AS s2 FROM DUAL
)
SELECT
  s1,
  s2,
  (SELECT COUNT(*) FROM my_tab WHERE serialnr BETWEEN r.s1 AND r.s2) AS cnt
FROM ranges r
ORDER BY s1;

Upvotes: 0

HEEN
HEEN

Reputation: 4721

I guess you need to user braces between each statement conditioning with OR like below

( 
  SERIALNR BETWEEN '93266070760000' AND   '93266070809999'
  OR
..... other ranges comes here
) and
OTHER cONDITIONS

You can see the documentation about logical conditions

Upvotes: 0

Jim Macaulay
Jim Macaulay

Reputation: 5141

You have use OR clause,

SELECT  count (SERIALNR) FROM my_tab WHERE 
(SERIALNR  BETWEEN '93266070760000' AND   '93266070809999')
OR
(SERIALNR  BETWEEN '93266070610000' AND   '93266070659999')
OR
(SERIALNR  BETWEEN '93266070660000' AND   '93266070709999')
OR
(SERIALNR  BETWEEN '93266070710000' AND   '93266070759999')
OR
(SERIALNR  BETWEEN '93266070760000' AND   '93266070809999');

If these values are stored in any other table, it would be more convenient

Upvotes: 0

Ankit Bajpai
Ankit Bajpai

Reputation: 13509

What about multiple or conditons -

SELECT  count (SERIALNR)
FROM my_tab
WHERE (SERIALNR BETWEEN '93266070760000' AND   '93266070809999'
       OR SERIALNR BETWEEN '93266070560000' AND   '93266070609999'
       OR SERIALNR BETWEEN '93266070610000' AND   '93266070659999'
       OR SERIALNR BETWEEN '93266070660000' AND   '93266070709999'
       OR SERIALNR BETWEEN '93266070710000' AND   '93266070759999'
       OR SERIALNR BETWEEN '93266070760000' AND   '93266070809999');

Upvotes: 0

Related Questions