Get most common value for each value of another column in SQL

by

It is now even simpler: PostgreSQL 9.4 introduced the mode() function:

select mode() within group (order by food_id)
from munch
group by country

returns (like user2247323’s example):

country | mode
--------------
GB      | 3
US      | 1

See documentation here:
https://wiki.postgresql.org/wiki/Aggregate_Mode

https://www.postgresql.org/docs/current/static/functions-aggregate.html#FUNCTIONS-ORDEREDSET-TABLE

More Related Contents:

  1. Select first row in each GROUP BY group?
  2. Optimize GROUP BY query to retrieve latest row per user
  3. How to select id with max date group by category in PostgreSQL?
  4. SQL query to get most recent row for each instance of a given key
  5. Optimize groupwise maximum query
  6. Get values from first and last row per group
  7. PostgreSQL: top n entries per item in same table
  8. Conditional lead/lag function PostgreSQL?
  9. Does PostgreSQL support “accent insensitive” collations?
  10. PostgreSQL: running count of rows for a query ‘by minute’
  11. IN vs ANY operator in PostgreSQL
  12. Split column into multiple rows in Postgres
  13. Postgres Dynamic Query Function
  14. How do I delete a fixed number of rows with sorting in PostgreSQL?
  15. What is the equivalent PostgreSQL syntax to Oracle’s CONNECT BY … START WITH?
  16. Error: Column does not exist
  17. DROP FUNCTION without knowing the number/type of parameters?
  18. Multiple array_agg() calls in a single query
  19. Selecting all corresponding fields using MAX and GROUP BY
  20. Calculate working hours between 2 dates in PostgreSQL
  21. How to show row numbers in PostgreSQL query?
  22. How do I ALTER a PostgreSQL table and make a column unique?
  23. Find records where join doesn’t exist
  24. Rails 4 LIKE query – ActiveRecord adds quotes
  25. PostgreSQL Index on JSON
  26. How do I speed up counting rows in a PostgreSQL table?
  27. Return rows from INSERT with ON CONFLICT without needing to update
  28. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  29. Fetch records that are non zero after the decimal point in PostgreSQL
  30. Postgres and Indexes on Foreign Keys and Primary Keys

Leave a Comment