How to aggregate rows from table to the one string?-CodePudding

I have two tables for Bank database: clients and accounts.

clients: 

|surname|city_name|client_id|
|Zubenko|Moscow   |    1    |
|Dorin  |Stavropol|    2    |
|Brylska|Moscow   |    3    |

accounts:

|acc_id|client_id|currency_type|available_amount|
|  1   |   1     |    RUB      |    34012       |
|  2   |   1     |    USD      |    5699        |
|  3   |   3     |    GBP      |    140000      |
|  4   |   3     |    GBP      |    -1133242    |
|  5   |   2     |    RUB      |    -130322     |
|  6   |   2     |    EUR      |    -19922      |

What I need is to make a function, which result is a table. Function must return list of clients, which have more than one account (acc_id) and at least one of the accounts has negative available_amount. Returned table needs to contain three columns: client_id, surname and accounts. The last column is a string in the next format (for every client_id):

acc_id_1 (currency_type_1): available_amount_1; acc_id_2 (currency_type_2): available_amount_2; ...

So for my example answer is:

|client_id|surname|accounts|
|   2     | Dorin |5 (RUB): -130322; 6 (EUR): -19922|
|   3     |Brylska|3 (GBP): 140000; 4 (GBP): -1133242|

My attempt is:

SELECT array_to_string(array_agg(acc_id), ', ')
from
(SELECT client_id, surname, acc_id, currency_type, available_amount
FROM
(   SELECT a.client_id, a.acc_id, a.currency_type,a.available_amount
    FROM accounts a, accounts b 
    where a.available_amount <0
    GROUP BY a.client_id, a.acc_id,a.currency_type, a.available_amount
    HAVING COUNT(a.acc_id) > 1) acc NATURAL JOIN clients) boo
group by client_id

Which returns

|array_to_string|
|5, 6|
|4|

So it seems to overcomplicated and not elegant. Moreover, this query does not counts positive available_amount (only negative ones) and don't solve my task (I need a table). How can I solve my task?

UPD:

I've made more elegant query, but it remains unknown how to make the last column in string-format, which I described:

select client_id, array_to_string(array_agg(acc_id), ', ') accounts
from accounts
where client_id in (SELECT distinct a.client_id
                    FROM accounts a, accounts b 
                    where a.available_amount <0)
group by client_id

which returns

|client_id|accounts|
|2|5, 6|
|3|3, 4|

CodePudding user response：

something like that

Select client_id,surname, acc_list 
from
(Select 
  client_id,surname, 
  count(*) as all_count, 
  Sum(case when available_amount<0 then 1 else 0 end) as neg_count,
  String_agg (..) as acc_list
from clients c join amounts a on c.clinet_id=a.client_id group by 1,2 ) t
where all_count>1 and neg_count>1