CODEWITHSUNDEEP
postgresql
user3178137
user3178137

Reputation: 963

How to determine most often element in postgreSQL?

If we have a column similar to this:

2012
2013
2012
2012
2011

How to determine which year is more used than the others? (in this example it would be 2012, since there are 3 entries with this year).

Upvotes: 0

Views: 83

Answers (3)

hd1
hd1

Reputation: 34677

Use R:

# get your data into R first
table(as.numeric(dataFromDb$column))

Upvotes: 0

Houari
Houari

Reputation: 5641

Something like:

select my_col_date, rank() over (order by used_count desc) 
from
(
SELECT my_col_date, count(my_col_date) used_count
  FROM my_table
  group by 1
) A
limit 1

OR

SELECT my_col_date, count(my_col_date) used_count
  FROM my_table
  group by 1
  order by count(my_col_date) limit 1

Upvotes: 0

krokodilko
krokodilko

Reputation: 36107

One way is to calculate numbers of occurences for all elements
order them by this number in descending order, and take the first one:

SELECT element
FROM table1
GROUP BY element
ORDER BY count(*) DESC
LIMIT 1
;

demo: http://sqlfiddle.com/#!15/47e59/2

Upvotes: 1

Related Questions