SQL query to get all the data in specific range.

Question

I have a table(offer) with three columns, id, product_id and price.

offer
----- 
id (integer)   
product_id (integer)
price (decimal)

I wanted to fire a SQL query which will return the number of offers between a price range. range should be like 0-1, 1-2, 2-3 etc

price_lower   price_upper    number_of_offers
-------------------------------------------------------------
0                     1            4
1                     2            1
2                     3            0
3                     4            6
4                     5            2
... etc

I did this to get the number of offers between 0 and 1

SELECT * FROM offer WHERE price BETWEEN 0 and 1;

What should be the query to get desired result.

Any sort of help will be appreciated. Thanks in advance.

Answer 1

SELECT SUM(number_of_offers) FROM offer WHERE price_lower >= [lowest price] AND price_upper <= [highest price].

The bits in square brackets you will have to fill in, for example you might substitue parameters there.

Answer 2

This should be correct. One caveat is that if anything costs an integral amount, it will show up depending on your flavor of sql because "between" is sometimes inclusive.

Answer 3

To get the output you listed:

SELECT MIN(t.price) 'price_lower',
       MAX(t.price) 'price_upper',
       COUNT(*) 'number_of_offers'
  FROM OFFER t
 WHERE t.price BETWEEN 0 AND 1
UNION ALL
SELECT MIN(t.price) 'price_lower',
       MAX(t.price) 'price_upper',
       COUNT(*) 'number_of_offers'
  FROM OFFER t
 WHERE t.price BETWEEN 1 AND 2
UNION ALL
...

...adding distinct SQL statements for each grouping you want, changing the WHERE clause to suit.

Answer 4

You can try something like this

DECLARE @Offer TABLE(
     ID INT IDENTITY (1,1),
     Product_ID INT,
     Price FLOAT
)

INSERT INTO @Offer SELECT 1, 0
INSERT INTO @Offer SELECT 1, .25
INSERT INTO @Offer SELECT 1, .5
INSERT INTO @Offer SELECT 1, .75
INSERT INTO @Offer SELECT 1, 1.
INSERT INTO @Offer SELECT 1, 1.25
INSERT INTO @Offer SELECT 1, 1.5
INSERT INTO @Offer SELECT 1, 1.75
INSERT INTO @Offer SELECT 1, 2.

INSERT INTO @Offer SELECT 2, 1
INSERT INTO @Offer SELECT 2, 1.25
INSERT INTO @Offer SELECT 2, 1.5
INSERT INTO @Offer SELECT 2, 1.75
INSERT INTO @Offer SELECT 2, 2.
INSERT INTO @Offer SELECT 2, 2.25
INSERT INTO @Offer SELECT 2, 2.5
INSERT INTO @Offer SELECT 2, 2.75
INSERT INTO @Offer SELECT 2, 3.

SELECT  Product_ID,
     FLOOR(Price) StartPrice,
     FLOOR(Price) + 1 EndPrice,
     COUNT(1) NumberItems
FROM    @Offer
GROUP BY Product_ID,
     FLOOR(Price)
ORDER BY 1, 2

Answer 5

I used:

select floor(price) as price_lower,
       ceiling(price) as price_upper,
       count(*) as offercount
   from tbl_offer
   group by floor(price), ceiling(price)

For the following test data:

insert into tbl_offer ([product_id],[price]) values (1, 1.9)
insert into tbl_offer ([product_id],[price]) values (2, 2.2)
insert into tbl_offer ([product_id],[price]) values (3, 2.3)
insert into tbl_offer ([product_id],[price]) values (4, 4.5)
insert into tbl_offer ([product_id],[price]) values (5, 2.7)

I got the following results:

price_lower price_upper offercount  
----------- ----------- ----------- 
1           2           1
2           3           3
4           5           1

The only thing missing from the example table you gave is that I don't have a line with price_lower 3, price_upper 4, offercount 0.

Answer 6

create table offer_range (price_lower integer, price_upper integer);

insert into offer_range (price_lower, price_upper) 
values (0, 1), (1, 2), (2, 3), (3, 4), (4, 5), -- ...etc
;

select r.price_lower,
r.price_upper,
(
select count(*)
from offer o 
where o.price between r.price_lower and r.price_upper
) as number_of_offers
from offer_range r
;

Answer 7

This one allows you to set a separate PriceRange table with ranges for each product.

DECLARE @Offer TABLE(
     ID INT IDENTITY (1,1)
     ,Product_ID INT
     ,Price decimal(8,2)
)

DECLARE @PriceRange TABLE(
     ID INT IDENTITY (1,1)
     ,Product_ID INT
     ,price_lower decimal(8,2)
     ,price_upper decimal(8,2)
)

SELECT x.Product_ID,  x.price_lower, x.price_upper, COUNT(*) as "number_of_offers"
   FROM 
    (
    SELECT o.Product_ID, o.Price, p.price_lower, p.price_upper,
      CASE 
        WHEN o.Price >= p.price_lower and o.Price < p.price_upper THEN 1
        ELSE 0
      END AS InRange           
    FROM @Offer AS o
      JOIN  @PriceRange as p ON o.Product_ID = p.Product_ID
    ) AS x
WHERE x.InRange = 1
GROUP BY x.Product_ID, x.price_lower, x.price_upper

Answer 8

While Checking all the answers. I have managed to write the query of course with your help. As few of you suggested to create a new table to store offer range which i don't want to do.

So, here is the SQL query what i wanted:

SELECT price as price_lower, (price + 1) as price_upper, (SELECT count(*) from offer WHERE price BETWEEN o.price and (o.price + 0.99)) from offer o GROUP BY price;

Thanks to all for your great efforts. You guys rock.

Answer 9

If you need specify a max number of ranges, please try this approach: You can indicate a max number of price ranges and it's automatically created the group price ranges and totals.

declare @Max_Steps int
set @Max_Steps= 6

select Price_Group * data.Price_Step as price_lower, (Price_Group+1)*data.Price_Step as price_upper, count(id) as number_of_offers
from
(
    select id, price, ceiling(price/Price_Step) as Price_Group, Price_Step
    from
    (
     select cast((max(price) - min(price))/@Max_Steps as decimal(15,2))  as Price_Step
     FROM offer
    ) PriceRangeCalculator
cross join  
    (
     SELECT id, price
     FROM offer
    ) SourceData
) data
group by data.Price_Group, data.Price_Step
order by data.Price_Group