MaTaKazer MaTaKazer -4 years ago 123
SQL Question

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;

Answer Source

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download