The forum will be down for maintenance over the weekend of August 18-20, 2017. The forum will be shut down on the evening (EDT) of Friday, August 18. Downtime is unknown but may be up to two days. The forum will be restarted as soon as maintenance is complete.

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:

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's gravatar image

accept rate: 0%

edited 02 Mar '14, 13:52

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)...

   DROP TABLE tblBill;

   DROP 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 );


-- 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 ),
              ORDER BY tblBatch_sum.batchnr 
            ) AS Batchnr
  FROM tblBill
       INNER JOIN ( SELECT tblBatch.tr_id,
                           SUM ( tblBatch.quant ) AS sum_quant
                      FROM tblBatch
                     GROUP BY tblBatch.tr_id,
                  ) 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
permanent link

answered 02 Mar '14, 16:09

Breck%20Carter's gravatar image

Breck Carter
accept rate: 21%


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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • 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:


question asked: 02 Mar '14, 12:47

question was seen: 788 times

last updated: 03 Mar '14, 09:14