PostgreSQL - return most common value for all columns in a table

Question

I've got a table with a lot of columns in it and I want to run a query to find the most common value in each column.

Ordinarily for a single column, I'd run something like:

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

Does PostgreSQL have a built in function for doing this or can anyone suggest a query I could run to achieve this?

Answer 1

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

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 the values in the same row, with the columns having its original name.

For more columns just had more subquerys as:

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

Edit:

You could reach it with window functions also. However it will not be better in performance nor in readability.