PostgreSQL - returns the most common value for all columns in a table

Question

I have a table with many columns and I want to run a query to find the most common value in each column.

Usually for a single column, I run something like:

SELECT country
FROM users
GROUP BY country
ORDER BY count(*) DESC
LIMIT 1

Does PostgreSQL have a built-in function for this, or can someone suggest a query that I could execute to achieve this?

Answer 1

Using the same query, for more than one column, you should:

SELECT *
FROM
(
    SELECT country
    FROM users
    GROUP BY 1
    ORDER BY count(*) DESC
    LIMIT 1
) country
,(
    SELECT city
    FROM users
    GROUP BY 1
    ORDER BY count(*) DESC
    LIMIT 1
) city

This works for any type and will return all values ​​on the same row, with the columns having their original name.

For more columns, there were simply more subqueries like:

,(
    SELECT someOtherColumn
    FROM users
    GROUP BY 1
    ORDER BY count(*) DESC
    LIMIT 1
) someOtherColumn

Edit:

. .

Answer 2

, :

SELECT 'country', country
FROM users
GROUP BY country
ORDER BY count(*) DESC
LIMIT 1
UNION ALL
SELECT 'city', city
FROM USERS
GROUP BY city
ORDER BY count(*) DESC
LIMIT 1
-- etc.

, , . , , , .

Answer 3

. . , OPs, , . SQL Fiddle

select distinct on (country_count, age_count) *
from (
    select
        country,
        count(*) over(partition by country) as country_count,
        age,
        count(*) over(partition by age) as age_count
    from users
) s
order by country_count desc, age_count desc
limit 1

Answer 4

PG 9.4 :

mode() WITHIN GROUP (ORDER BY sort_expression)

( , )

...

  CREATE OR REPLACE FUNCTION mode_array(anyarray)
            RETURNS anyelement AS
$BODY$
    SELECT a FROM unnest($1) a GROUP BY 1 ORDER BY COUNT(1) DESC, 1 LIMIT 1;
$BODY$
LANGUAGE SQL IMMUTABLE;



CREATE AGGREGATE mode(anyelement)(
   SFUNC = array_append, --Function to call for each row. Just builds the array
   STYPE = anyarray,
   FINALFUNC = mode_array, --Function to call after everything has been added to array
   INITCOND = '{}'--Initialize an empty array when starting
) ;

: SELECT mode(column) FROM table;