1

what I want to do is the following:

I have a table like this:

ID;    STATUS;    ORIGIN
1      READY      a
2      READY      b
3      OPEN       a
4      OPEN       a

This should be queried to:

IDS;   OPEN; READY; ORIGIN
1,3,4  2     1      a
2      0     1      b

The query so far:

SELECT ORIGIN,
  SUM(CASE WHEN UPPER(STATUS) = 'OPEN' THEN 1 ELSE 0 END) AS OPEN,
  SUM(CASE WHEN UPPER(STATUS) = 'READY' THEN 1 ELSE 0 END) AS READY,
  <LIST_FUNC>(ID, ',') AS IDS
FROM TABLE
GROUP BY ORIGIN;

I am looking for a function in derby (<LIST_FUNC>), that does the concatenation of the ID column during the group by aggregation. Of course, the signature may look different.

Thanks in advance!

Benny
  • 1,435
  • 1
  • 15
  • 33
  • 2
    I don't think Derby has a function equivalent to `string_agg()` or `group_concat()`. If you really need that, you need to [implement your own](http://db.apache.org/derby/docs/10.12/ref/rrefsqljcreateaggregate.html#rrefsqljcreateaggregate) or switch to HSQLDB or H2. Both have `group_concat()` for that. With HSQLDB you could even use the more concise `count(*) filter (where status = 'OPEN')` for the conditional aggregation –  Sep 15 '16 at 08:52
  • Ok, thanks! Derby again... – Benny Sep 15 '16 at 12:09

0 Answers0