CODEWITHSUNDEEP
sqlpostgresql
Gavin
Gavin

Reputation: 2824

array_agg() function on 2 columns

I have a table below

CUST_ID integer          | servername text        | DATE
---------------------    |---------------------------------------
    1                    | '1'                    | '2017-01-15'
    1                    | '1'                    | '2017-02-15'
    2                    | '1'                    | '2017-01-15'
    2                    | '2'                    | '2017-01-15'

I would like to extract the information out as such.

CUST_ID integer          | servername text        | DATE
-------------------------|------------------------|------------------------------
    1                    | '1'                    | {'2017-01-15', '2017-02-15'}
    2                    | {'1', '2'}             | '2017-01-15'

I have tried a GROUP BY CUST_ID, DATE UNION GROUP BY CUST_ID, SERVICE as such. Obviously I get an ERROR: UNION types text and text[] cannot be matched as in the first select query, the servername was not an array. 

SELECT CUST_ID, servername, array_agg(trans_date) FROM infomation
GROUP by CUST_ID, servername

UNION 
SELECT CUST_ID, array_agg(servername), trans_date FROM infomation
GROUP by CUST_ID, trans_date;

Upvotes: 2

Views: 1106

Answers (2)

Patrick
Patrick

Reputation: 32161

You are mixing text and text[] in the second column and date and date[] in the third column. That will never work. If you can work with arrays in all cases, then this should work:

SELECT cust_id,
       array_agg(DISTINCT servername) AS service,
       array_agg(DISTINCT trans_date) AS trans_date
FROM information
GROUP BY cust_id;

Upvotes: 2

David דודו Markovitz
David דודו Markovitz

Reputation: 44911

select      cust_id
           ,array_agg(distinct servername)      as servername
           ,array_agg(distinct date)            as date

from        information

group by    cust_id

+---------+------------+-------------------------+
| cust_id | servername |          date           |
+---------+------------+-------------------------+
|       1 | {"1"}      | {2017-01-15,2017-02-15} |
|       2 | {"1","2"}  | {2017-01-15}            |
+---------+------------+-------------------------+

Upvotes: 2

Related Questions