# List function with sum?

 I have a subquery that retunrns multiple lines. To solve it, I have used the List function. The separation of List function is comma. TblBill is in the main query with many other tables. ```(Select List(tblBatch.batchnr) From tblBatch where tblBatch.tr_id = tblBill.kfr_trans_id ) As Batchnr ``` This subquery returns: `B12, B13, B14, B43` and so on... Now I want to use SUM from another field (quant) from table tblBatch in the subquery and then separate those with a line break instead of comma. Batch number may occur several times and I want to summarize these I want the subquery return like this: ```---Batchnr--- 3 B12 4 B13 1 B14 1 B43 ``` and so on.. The number before batchnr is the sum from quant field. How do I solve it the best way? asked 02 Mar '14, 12:47 Rolle 439●31●34●46 accept rate: 0%

 Did your head explode as you tried to make the subquery work with LIST and SUM together? :) One way to do this is "divide and conquer": do the SUM separately from the LIST. One way to divide and conquer is to do the SUM first, inside a derived table in the FROM clause, and then the LIST in the outer query. Another valuable technique is to build a complex STRING() call inside the LIST() call, using CR-LF as the line separator... LIST() is one of the most powerful and most useful features in SQL Anywhere, a wonderful invention indeed! (fast, too... entire commercial web pages have been constructed with LIST). It was a bit difficult to reverse-engineer the table layouts from the question, but here goes (let me know if it doesn't match your needs at all)... ```BEGIN DROP TABLE tblBill; EXCEPTION WHEN OTHERS THEN END; BEGIN DROP TABLE tblBatch; EXCEPTION WHEN OTHERS THEN END; CREATE TABLE tblBill( kfr_trans_id INTEGER NOT NULL PRIMARY KEY ); CREATE TABLE tblBatch( tr_id INTEGER NOT NULL REFERENCES tblBill ( kfr_trans_id ), line_id INTEGER NOT NULL, batchnr VARCHAR ( 3 ) NOT NULL, quant INTEGER NOT NULL, PRIMARY KEY ( tr_id, line_id ) ); INSERT tblBill VALUES ( 10 ); INSERT tblBatch VALUES ( 10, 100, 'B12', 2 ); INSERT tblBatch VALUES ( 10, 200, 'B12', 1 ); INSERT tblBatch VALUES ( 10, 300, 'B13', 1 ); INSERT tblBatch VALUES ( 10, 400, 'B13', 2 ); INSERT tblBatch VALUES ( 10, 500, 'B13', 1 ); INSERT tblBatch VALUES ( 10, 600, 'B14', 1 ); INSERT tblBatch VALUES ( 10, 700, 'B43', 1 ); COMMIT; -- The original subquery displays duplicate tblBatch.batchnr values. SELECT tblBill.kfr_trans_id, (Select List(tblBatch.batchnr) From tblBatch where tblBatch.tr_id = tblBill.kfr_trans_id ) As Batchnr FROM tblBill; /* kfr_trans_id Batchnr 10 B12,B12,B13,B13,B13,B14,B43 */ -- Adding DISTINCT gets rid of the duplicates. SELECT tblBill.kfr_trans_id, (Select List( DISTINCT tblBatch.batchnr) From tblBatch where tblBatch.tr_id = tblBill.kfr_trans_id ) As Batchnr FROM tblBill; /* kfr_trans_id Batchnr 10 B12,B13,B14,B43 -- as posted in the question */ -- Use JOIN with derived table, then LIST and STRING together. SELECT tblBill.kfr_trans_id, LIST ( STRING ( tblBatch_sum.sum_quant, ' ', tblBatch_sum.batchnr ), '\X0D\X0A' ORDER BY tblBatch_sum.batchnr ) AS Batchnr FROM tblBill INNER JOIN ( SELECT tblBatch.tr_id, tblBatch.batchnr, SUM ( tblBatch.quant ) AS sum_quant FROM tblBatch GROUP BY tblBatch.tr_id, tblBatch.batchnr ) AS tblBatch_sum ON tblBatch_sum.tr_id = tblBill.kfr_trans_id GROUP BY tblBill.kfr_trans_id ORDER BY tblBill.kfr_trans_id; /* kfr_trans_id Batchnr 10 3 B12\x0d\x0a4 B13\x0d\x0a1 B14\x0d\x0a1 B43 */ -- In ISQL, check Tools - Options... - SQL Anywhere - Style - Text /* kfr_trans_id Batchnr ------------ ------- 10 3 B12 4 B13 1 B14 1 B43 (1 rows) Execution time: 0.031 seconds */ ``` answered 02 Mar '14, 16:09 Breck Carter 27.1k●455●621●894 accept rate: 21% Thanks! This works...but I wanna use a subquery and it allowed only one Select list item? How do I do then? (03 Mar '14, 02:37) Rolle
 toggle preview community wiki:

By Email:

Markdown Basics

• *italic* or _italic_
• **bold** or __bold__
• image?![alt text](/path/img.jpg "title")
• numbered list: 1. Foo 2. Bar
• to add a line break simply add two spaces to where you would like the new line to be.
• basic HTML tags are also supported

Question tags:

×12

question asked: 02 Mar '14, 12:47

question was seen: 944 times

last updated: 03 Mar '14, 09:14