How to split table records into aggregate and array in PostgreSQL

Question

I have a table max_values in my PostgreSQL 9.5 database containing four columns.

id  values  lower_limit   upper_limit
1   15.3    112           142
2   18.1    142           172
3   17.5    172           202
4   16.8    202           232
5   15.7    232           262
6   19.3    262           292
7   21.4    292           322
8   21.4    322           352
9   21.4    352           382
10  21.4    382           412
11  21.4    412           442
12  21.4    442           472

with table structure as follows:

Create Table max_values as
(
id integer,
values numeric,
lower_limit numeric,
upper_limit numeric
);

Each 'ID' is unique identifier of 'values'. I need to split above table records into an aggregate and an array based on ID (expected output: 1 row and 2 columns). What I need to do is:

• get the most prevalent/most repetitive value in 'values'
• count IDs of 'values' other than most repetitive value
• return the total ID count, lower limit, value and upper limit in an array
• if there is no repetitive value then first column could be zero

The desired output could be like:

Most_repetitive    Remaining
21.4               6, 112, 15.3, 142, 142, 18.1, 172, 172, 17.5, 202, 202, 16.8, 232, 232, 15.7, 262, 262, 19.3, 292

Being a newbie, I am aware of some PostgreSQL functions such as mode () etc. but I would highly appreciate if someone could point me how to get the desired output for my table records?

Answer 1

The first part is pretty easy:

select values,count(*) from max_values group by values order by 2 desc limit 1

The second part you could the ARRAY function to transform rows for all values that aren't equal to the one above into one long array, though that is honestly a little tough to do in one SQL statement. Does it really have to be one long array like that? What are you going to do with it? There is an example of ARRAY() here:

https://blog.lerner.co.il/turning-postgresql-rows-arrays-array/