How to Get Rows Having Sum Equal to Given Value

sql server : select rows who's sum matches a value

You can use recursive query in MSSQL to solve this.

SQLFiddle demo

The first recursive query build a tree of items with cumulative sum <= 150. Second recursive query takes leafs with cumulative sum = 150 and output all such paths to its roots. Also in the final results ordered by ItemsCount so you will get preferred groups (with minimal items count) first.

WITH CTE as
( SELECT id,num,
         id as Grp,
         0 as parent,
         num as CSum,
         1 as cnt,
         CAST(id as Varchar(MAX)) as path
     from T where num<=150
  UNION all
  SELECT t.id,t.num,
         CTE.Grp as Grp, 
         CTE.id as parent,
         T.num+CTE.CSum as CSum,
         CTE.cnt+1 as cnt,
         CTE.path+','+CAST(t.id as Varchar(MAX)) as path
    from T 
  JOIN CTE on T.num+CTE.CSum<=150 
             and CTE.id<T.id 
),
BACK_CTE as
(select CTE.id,CTE.num,CTE.grp, 
         CTE.path ,CTE.cnt as cnt,
         CTE.parent,CSum 
    from CTE where CTE.CSum=150
  union all
  select CTE.id,CTE.num,CTE.grp,
         BACK_CTE.path,BACK_CTE.cnt, 
         CTE.parent,CTE.CSum 
   from CTE
   JOIN BACK_CTE on CTE.id=BACK_CTE.parent 
              and CTE.Grp=BACK_CTE.Grp
              and BACK_CTE.CSum-BACK_CTE.num=CTE.CSum
) 
select id,NUM,path, cnt as ItemsCount   from BACK_CTE order by cnt,path,Id

SQL: How to select rows that sum up to certain value

You can use a correlated subquery to get the running total and retrieve those rows whose running total is < a specified number. (note that i changed the storage column to int. if it is a varchar the comparison would return the wrong result)

select id,user_id,storage
from uploads t
where storage+coalesce((select sum(storage) from uploads 
                        where storage<t.storage),0) < 410000
order by storage

SQL Fiddle

Edit: When there are duplicate values in the storage column, it has to be accounted for in the running sum by including a condition for the id column. (in this case < condition has been used, so the smallest id for a duplicate storage value gets picked up)

select id,user_id,storage
from uploads t
where storage+coalesce((select sum(storage) from uploads 
                        where storage<t.storage 
                        or (storage=t.storage and id < t.id)),0) < 410000
order by storage

Select all rows where the sum of column X is greather or equal than Y

You need a recursive query like this:

demo:db<>fiddle

WITH RECURSIVE lots_with_rowcount AS (       -- 1
    SELECT 
        *, 
        row_number() OVER (ORDER BY avail_qty DESC) as rowcnt
    FROM mytable
), lots AS (                                 -- 2
    SELECT                                   -- 3
        lot_nr,
        avail_qty,
        rowcnt,
        avail_qty as total_qty
    FROM lots_with_rowcount
    WHERE rowcnt = 1
    
    UNION
    
    SELECT 
        t.lot_nr,
        t.avail_qty,
        t.rowcnt,
        l.total_qty + t.avail_qty           -- 4
    FROM lots_with_rowcount t
    JOIN lots l ON t.rowcnt = l.rowcnt + 1
        AND l.total_qty < --<your demand here>
)
SELECT * FROM lots                          -- 5

1. This CTE is only to provide a row count to each record which can be used within the recursion to join the next records.
2. This is the recursive CTE. A recursive CTE contains two parts: The initial SELECT statement and the recursion.
3. Initial part: Queries the lot record with the highest avail_qty value. Naturally, you can order them in any order you like. Most qty first yield the smallest output.
4. After the UNION the recursion part: Here the current row is joined the previous output AND as an additional condition: Join only if the previous output doesn't fit your demand value. In that case, the next total_qty value is calculated using the previous and the current qty value.
5. Recursion end, when there's no record left which fits the join condition. Then you can SELECT the entire recursion output.

Notice: If your demand was higher than your all your available quantities in total, this would return the entire table because the recursion runs as long as the demanded is not reached or your table ends. You should add a query before, which checks this:

SELECT SUM(avail_qty) > demand FROM mytable

SQL query to get rows until sum of a column equal to some value

If you are running MySQL 8.0, you can do a window sum:

select *
from (
    select
        w.*,
        sum(weight) over(order by added_date desc, ware_id) running_weight
    from Warehouse w
) t
where running_weight > 272
order by added_date desc, ware_id;

The inner query computes a running sum of the weights, starting with the most recent record, and by descending added_date (we use ware_id to break the ties). The outer query pulls out all the records for which the running sum exceeds 272.

Demo on DB Fiddle

Your initial dataset has a total weight of 272.

Let's add a recent record with a weight of 100.

insert into Warehouse values (99, 100, '2019-11-23');

Now the query returns:

ware_id | weight | added_date | running_weight
------: | -----: | :--------- | -------------:
     19 |     27 | 2012-01-25 |            284
     80 |     14 | 2012-01-25 |            298
     22 |      2 | 2010-11-22 |            300
     10 |     11 | 2010-11-14 |            311
     42 |     40 | 2009-10-13 |            351
     56 |     21 | 2008-04-04 |            372

How to select rows which has cumulative sum of column value min to given value

We can use SUM here as an analytic function:

WITH cte AS (
    SELECT *, SUM(total_quantity) OVER (ORDER BY created_dttm DESC)
                  - total_quantity AS tq_sum
    FROM yourTable
)

SELECT id, type, total_quantity, created_dttm
FROM cte
WHERE tq_sum < 24;

Demo

The above trick (in the CTE) works by sparing the current row's total quantity from the running total. So the first row to exceed the threshhold of 24 would also be included, because its total quantity would be excluded from the running total.

How to select rows based on the sum of values in one column meeting a condition?

Use window functions:

select i.*
from (select i.*,
             sum(numberOfInteractions) over (partition by month, clientId) as month_cnt
      from interactions i
     ) i
where month_cnt > 4;

Select rows where sum of a column equals a fixed value in R

Here is another base R solution by defining a recursive function subsetSum (I guess this would be faster since it avoids checking through all combinations)

subsetSum <- function(v, target, r = c()) {
    if (sum(r) == target) {
        return(list(r))
    }
    unlist(lapply(seq_along(v), function(k) subsetSum(v[-(1:k)], target, c(r, v[k]))), recursive = FALSE)
}

Then, when running

target <- 20
lst <- subsetSum(setNames(df$quantity, seq(nrow(df))), target)
res <- Map(function(v) df[as.integer(names(v)), ], lst)

you will get

> res
[[1]]
  id quantity
2  2        1
3  3        4
4  4       12
7  7        3

[[2]]
  id quantity
2  2        1
5  5       19

[[3]]
  id quantity
2  2        1
7  7        3
9  9       16

[[4]]
  id quantity
3  3        4
7  7        3
8  8       13

[[5]]
  id quantity
3  3        4
9  9       16

---

If you want only one of the subset sum which reaches the given value, you can try subsetsum from package adagio

library(adagio)
target <- 20
res <- df[subsetsum(df$quantity,target)$inds,]

which gives

> res
  id quantity
2  2        1
5  5       19

Only return rows if sum is greater than a value

Whenever you need to do a "WHERE" clause on an aggregate (which SUM is), you need to use the HAVING clause.

SELECT PPOLNO, SUM(PPRMPD) AS SUM FROM PFNTLPYMTH
WHERE ((PYEAR=2012 AND PMONTH >=3 AND PDAY >=27) OR (PYEAR=2013 
AND PYEAR <=3 AND PDAY<=27)) GROUP BY PPOLNO
HAVING SUM(PPRMPD) >= 5000

---

Related Topics