Find Mode in Postgres SQL

Question

I have a table like this -

cdr_pkey as serial, calldate as timestamp, src as text, duration as bigint

I need to find top 10 most frequent numbers in 'src' column, together with the number of occurrence in the table by using SQL query.

I tried by exporting to Excel and run Mode function but the records are around 7 million so it is not so efficient.

PS. I'm using PostreSQL 9.1

@chl Thanks but not really. I prefer to have a step by step explanation so I can learn. — Ye Myat Aung
– Ye Myat Aung, Commented Dec 10, 2012 at 10:24

mlissner · Accepted Answer · 2014-10-03 17:33:48Z

2

select 
    src as text,
    count(*) as total
from t
group by 1
order by total desc
limit 10;

mlissner

18.3k18 gold badges121 silver badges177 bronze badges

answered Dec 10, 2012 at 10:31

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1