0

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?

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/

  • Well, thanks for the first part though. What I need is, (1) get the most frequent value (2) while for remaining (other than most frequent), generate an array of: 'lower_limit, value, upper_limit'. Thus, I would need to generate an array. Whether it's long or not, it depends on the repeated values. – khajlk Jun 9 '17 at 13:56
  • In my array, 'lower + value + upper' makes one count then I think it is also possible to count array elements and return them as a separate column as "count". Correct? – khajlk Jun 9 '17 at 13:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.