Home » SQL & PL/SQL » SQL & PL/SQL » Retrieving rows for which SUM(qty) equals to a specified value ?
Retrieving rows for which SUM(qty) equals to a specified value ? [message #183128] Wed, 19 July 2006 08:32 Go to next message
fvandewalle
Messages: 2
Registered: July 2006
Location: Rouen - FRANCE
Junior Member
Let's consider a basic table as with 3 fields : item (pk), qty, salesrep (fk) :
item qty salesrep
**** *** **********
itemA 1 fred
itemB 2 john
itemC 2 fred
itemD 1 fred
itemE 3 fred

I want to know belong fred's items, which ones to pick up in order to reach a total qty of 5. Answers are itemC/itemE or itemA/itemD/itemE. I tried with various combinations of SUM,MAX,HAVING without any success (PS: running on 9iEE).
Re: Retrieving rows for which SUM(qty) equals to a specified value ? [message #183143 is a reply to message #183128] Wed, 19 July 2006 10:03 Go to previous messageGo to next message
Art Metzer
Messages: 2478
Registered: December 2002
Senior Member
You can probably modify the solution proposed here.
Re: Retrieving rows for which SUM(qty) equals to a specified value ? [message #183784 is a reply to message #183143] Sun, 23 July 2006 16:18 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@10gXE> SELECT * FROM basic_table
  2  /

ITEM         QTY SALESREP
----- ---------- --------
itemA          1 fred
itemB          2 john
itemC          2 fred
itemD          1 fred
itemE          3 fred

SCOTT@10gXE> CREATE OR REPLACE FUNCTION calc
  2    (p_string IN VARCHAR2)
  3    RETURN	    NUMBER
  4  AS
  5    v_result     NUMBER;
  6  BEGIN
  7    EXECUTE IMMEDIATE
  8  	 'BEGIN :b_result := ' || p_string || '; END;'
  9    USING OUT v_result;
 10    RETURN v_result;
 11  END;
 12  /

Function created.

SCOTT@10gXE> COLUMN items FORMAT A30
SCOTT@10gXE> SELECT items, total
  2  FROM   (SELECT SUBSTR (SYS_CONNECT_BY_PATH (item, '/'), 2) AS items,
  3  		    calc
  4  		      (SUBSTR (SYS_CONNECT_BY_PATH (qty, '+'), 2)) AS total
  5  	     FROM   basic_table
  6  	     START  WITH salesrep = 'fred'
  7  	     CONNECT BY PRIOR salesrep = salesrep
  8  		     AND PRIOR item < item)
  9  WHERE  total = 5
 10  /

ITEMS                               TOTAL
------------------------------ ----------
itemA/itemD/itemE                       5
itemC/itemE                             5

SCOTT@10gXE> 


Re: Retrieving rows for which SUM(qty) equals to a specified value ? [message #183884 is a reply to message #183784] Mon, 24 July 2006 04:21 Go to previous messageGo to next message
zozogirl
Messages: 77
Registered: November 2005
Location: Seoul, Korea
Member

Without creating any functions, we can do this.

SELECT items
     , LENGTH (total) total
  FROM (SELECT     SUBSTR (SYS_CONNECT_BY_PATH (item, '/'), 2) AS items
                 , REPLACE (SYS_CONNECT_BY_PATH (LPAD ('X', qty, 'X'), '/')
                          , '/') AS total
              FROM basic_table
        START WITH salesrep = 'fred'
        CONNECT BY PRIOR salesrep = salesrep
               AND PRIOR item < item)
 WHERE LENGTH (total) = 5


ITEMS                     TOTAL
----------------- -------------
itemA/itemD/itemE             5
itemC/itemE                   5

Query Your Dream & Future at SoQooL
http://www.soqool.com
Re: Retrieving rows for which SUM(qty) equals to a specified value ? [message #183944 is a reply to message #183884] Mon, 24 July 2006 08:38 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8632
Registered: November 2002
Location: California, USA
Senior Member
Very clever solution. I like it. You post a lot of good ideas.
Re: Retrieving rows for which SUM(qty) equals to a specified value ? [message #302017 is a reply to message #183128] Fri, 22 February 2008 12:59 Go to previous message
Frank_Zhou
Messages: 5
Registered: February 2008
Location: Braintree , MA
Junior Member
zozogirl's query will works nicely, if the QTY is a small number.
The SYS_CONNECT_BY_PATH function has a 4k length limitation, so if the 'QTY' is a big number
The sql solution from above will not be able to handle that case.


The alernative solution for this problem is based on my SQL Query for the "Combination_Sum" problem.

http://www.jlcomp.demon.co.uk/faq/Combination_Sum.html

The following SQL solution switches the 4K limitation from total "QTY" amount to total length of the ITEMS names
for each combination. The "connect by " clause in the query is also optimized.

SELECT path ITEMS, sum(n) TOTAL
FROM 
(SELECT path, doc.extract('/l/text()').getNumberVal() n   
 FROM
(SELECT ltrim(SYS_CONNECT_BY_PATH (QTY , ','), ',') AS str,
        ltrim(SYS_CONNECT_BY_PATH (ITEM, '/'),'/') AS path
  FROM basic_table
  START WITH salesrep = 'fred'
  CONNECT BY PRIOR salesrep = salesrep
  AND PRIOR item < item
  AND CASE 
       WHEN LEVEL = 2
       THEN CASE WHEN QTY + PRIOR QTY <= 5 THEN 1 END
       WHEN LEVEL BETWEEN 3 AND 5 
       THEN CASE WHEN CONNECT_BY_ROOT QTY + PRIOR QTY + QTY <= 5 THEN 1 END
       ELSE 1 END =1
  AND LEVEL <=5
) t,
TABLE(xmlSequence(extract(XMLType('<doc><l>'||
     replace(t.str,',','</l><l>')||'</l></doc>'),'/doc/l'))) doc
)
GROUP BY path
HAVING  sum(n) = 5


ITEMS                               TOTAL
------------------------------ ----------
itemA/itemD/itemE                       5
itemC/itemE                             5
 

[Updated on: Fri, 22 February 2008 13:39] by Moderator

Report message to a moderator

Previous Topic: last 3 charecters
Next Topic: Extract data from query insert in table, if Null insert other values
Goto Forum:
  


Current Time: Mon Dec 05 21:12:03 CST 2016

Total time taken to generate the page: 0.08317 seconds