CODEWITHSUNDEEP
sql
Chaerul Bachri
Chaerul Bachri

Reputation: 1

Query return rows whose sum of column value match given sum

I have tables with:

id   desc   total
1   baskets 25
2   baskets 15
3   baskets 75
4   noodles 10

I would like to ask the query with output which the sum of total is 40.

The output would be like:

id   desc   total
1   baskets 25
2   baskets 15

Upvotes: 0

Views: 369

Answers (5)

zhangrt
zhangrt

Reputation: 1

select desc 
from (select desc, sum(total) as ct group by desc)

Upvotes: 0

Matthew Cox
Matthew Cox

Reputation: 13682

From reading your question, it sounds like you want a query that returns any subset of of sums that represent a certain target value and have the same description.

There is no simple way to do this. This migrates into algorithmic territory.

Assuming I am correct in what you are after, group bys and aggregate functions will not solve your problem. SQL cannot indicate that a query should be performed on subsets of data until it exhaust all possible permutations and finds the Sums that match your requirements.

You will have to intermix an algorithm into your sql ... i.e a stored procedure.

Or simply get all the data from the database that fits the desc then perform your algorithm on it in code.

I recall there was a CS algorithmic class I took where this was a known Problem:

I believe you could just adapt working versions of this algorithm to solve your problem

http://en.wikipedia.org/wiki/Subset_sum_problem

Upvotes: 1

The Scrum Meister
The Scrum Meister

Reputation: 30131

Not quite sure what you want, but this may get you started

SELECT `desc`, SUM(Total) Total
FROM TableName
GROUP BY `desc`
HAVING SUM(Total) = 40

Upvotes: 1

Crimsonland
Crimsonland

Reputation: 2204

Select Desc,SUM(Total) as SumTotal
from Table
group by desc
having SUM(Total)  > = 40

Upvotes: 1

nybbler
nybbler

Reputation: 4841

I believe this will get you a list of the results you're looking for, but not with your example dataset because nothing in your example dataset can provide a total sum of 40. 

SELECT id, desc, total 
FROM mytable 
WHERE desc IN (
  SELECT desc 
  FROM mytable 
  GROUP BY desc 
  HAVING SUM(total) = 40
)

Upvotes: 1

Related Questions