CODEWITHSUNDEEP
sqlsql-servergroup-byhaving
Shushil Bohara
Shushil Bohara

Reputation: 5656

Fetch records that are not greater than provided quantity

Quantity comes as a parameter so I have to fetch records only up to @quantity. What I tried after FROM is not so meaningful so given below is my query where I need to implement that condition. I tried both in WHERE and using HAVING but unable:

SELECT prod_id,
    product,
    technology,
    price,
    quantity
FROM tbl_product
------SUM(quantity) <= @quantity

SAMPLE DATA:

prod_id product technology  price   quantity
1       M1      ST          25.33   10
2       M2      ST          23.65   50
3       S1      ST          54.32   90
4       S2      PY          21.435  30
5       S3      PY          98.64   110

IF @quantity = 150 then first 3 records should be fetched.

Upvotes: 0

Views: 75

Answers (3)

Xingzhou Liu
Xingzhou Liu

Reputation: 1559

You need to get a cumulative sum(based on the "fetch up to" in your question), which is most efficiently done using window function.

try:

with cumulative as (
    SELECT prod_id,
      product,
      technology,
      price,
      quantity,
      sum(quantity) over (order by <order column>) as cumulative_quantity
    FROM tbl_product
) 
select 
    prod_id,
    product,
    technology,
    price,
    quantity
from cumulative where cumulative_quantity <= @quantity;

You'll need to provide an order column to indicate what ordering do you want the cumulative summation to use.

So when @quantity = 150, your result will be:

prod_id product technology  price   quantity
1       M1      ST          25.33   10
2       M2      ST          23.65   50
3       S1      ST          54.32   90

(if you are using prod_id as the order by column).

Upvotes: 2

Uttam Kasundara
Uttam Kasundara

Reputation: 237

Based on product you can find as below 

SELECT distinct prod_id,
       product,
       technology,
       price,
FROM tbl_product
where product in (
SELECT product
FROM tbl_product
GROUP BY product
HAVING SUM(quantity) <= @quantity)

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521194

Assuming you want to take the first N records as ordered by prod_id whose cumulative sum is less than @quantity, then you can try the following query:

SELECT
    prod_id,
    product,
    technology,
    price,
    quantity
FROM tbl_product t1
WHERE (SELECT SUM(t2.quantity) FROM tbl_product t2
       WHERE t2.prod_id <= t1.prod_id) < @quantity

Upvotes: 0

Related Questions