GROUP BY and COUNT using ActiveRecord

Distinct and Group By are going to give you different results. To get the results you expect you’ll want to use

Person.group(:name).count
(1.2ms)  SELECT COUNT(*) AS count_all, name AS name FROM "people" GROUP BY "people"."name"
=> {"Dan"=>3, "Dave"=>2, "Vic"=>1}

Seen above, group will return things as a hash. While distinct just returns the number of people in total, seen below.

Person.distinct(:name).count
(0.4ms)  SELECT DISTINCT COUNT(DISTINCT "people"."id") FROM "people"
=> 6

Related Posts:

  1. missing FROM-clause entry for table
  2. Insert text with single quotes in PostgreSQL
  3. ERROR: there is no unique constraint matching given keys for referenced table “bar”
  4. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  5. Postgresql tables exists, but getting “relation does not exist” when querying
  6. postgresql: error duplicate key value violates unique constraint
  7. pg_ctl: no database directory specified and environment variable PGDATA unset
  8. Postgres Error: More than one row returned by a subquery used as an expression
  9. What is the difference between LATERAL JOIN and a subquery in PostgreSQL?
  10. PostgreSQL create table if not exists
  11. updating table rows in postgres using subquery
  12. PostgreSQL visual interface similar to phpMyAdmin?
  13. Postgresql column reference “id” is ambiguous
  14. What is a postgres superuser
  15. What is a stored procedure?
  16. Unknown column in ‘field list’ error on MySQL Update query
  17. SQL Inner-join with 3 tables?
  18. Advanced AREL or just Rails Query for has_many through search by association
  19. Conversion failed when converting date and/or time from character string while inserting datetime
  20. MySQL create table if not exists and insert record only if table was created
  21. error, string or binary data would be truncated when trying to insert
  22. ORA-00907: missing right parenthesis
  23. Rename column SQL Server 2008
  24. Why do we need “Relationships” between tables at all?
  25. PostgreSQL “DESCRIBE TABLE”
  26. Using group by on multiple columns
  27. How to declare a variable in MySQL?
  28. ORA-01843 not a valid month- Comparing Dates
  29. How do I escape a single quote in SQL Server?
  30. Teradata: how to convert varchar value (format ‘dd.mm.yyyy’) to date (format ‘yyyy-mm-dd’ )?
  31. How Stuff and ‘For Xml Path’ work in SQL Server?
  32. Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)
  33. Oracle SELECT TOP 10 records
  34. Getting error: Peer authentication failed for user “postgres”, when trying to get pgsql working with rails
  35. must appear in the GROUP BY clause or be used in an aggregate function
  36. How can I return pivot table output in MySQL?
  37. The multi-part identifier could not be bound
  38. MySQL syntax for Join Update
  39. SQLite – UPSERT *not* INSERT or REPLACE
  40. how to drop partition without dropping data in MySQL?
  41. Simple way to calculate median with MySQL
  42. The ALTER TABLE statement conflicted with the FOREIGN KEY constraint
  43. Error 1046 No database Selected, how to resolve?
  44. What is it exactly a BLOB in a DBMS context
  45. How do I do multiple CASE WHEN conditions using SQL Server 2008?
  46. Simple Random Samples from a Sql database
  47. SQL Server IF NOT EXISTS Usage?
  48. How to insert date values into table
  49. Arithmetic overflow error converting numeric to data type numeric
  50. I want to use CASE statement to update some records in sql server 2005
  51. could not connect to server: No such file or directory (PG::ConnectionBad)
  52. SELECT DISTINCT on one column
  53. Rails 4 RoutingError: No Route Matches
  54. how to remove time from datetime
  55. TSQL PIVOT MULTIPLE COLUMNS
  56. MySQL – UPDATE multiple rows with different values in one query
  57. How to create a MySQL hierarchical recursive query?
  58. Microsoft OLE DB Provider for SQL Server error ‘80004005’
  59. Query to convert from datetime to date mysql
  60. How to query MongoDB with “like”
  61. Equivalent of Oracle’s RowID in SQL Server
  62. Subtract one day from datetime
  63. MySQL equivalent of DECODE function in Oracle
  64. what is the difference between triggers, assertions and checks (in database)
  65. Replacing NULL with 0 in a SQL server query
  66. How to delete from multiple tables in MySQL?
  67. How to copy a row and insert in same table with a autoincrement field in MySQL?
  68. How do I query for all dates greater than a certain date in SQL Server?
  69. Backup a single table with its data from a database in sql server 2008
  70. Error converting data type varchar
  71. I want to use CASE statement to update some records in sql server 2005
  72. Cannot create an instance of OLE DB provider Microsoft.Jet.OLEDB.4.0 for linked server null
  73. ORDER BY items must appear in the select list if SELECT DISTINCT is specified
  74. ROW_NUMBER() in MySQ
  75. Rails button_to vs. HTML Button Tag
  76. Foreign key references invalid table
  77. MySQL INSERT INTO table VALUES.. vs INSERT INTO table SET
  78. Is there a coalesce-like function in Excel?
  79. How do I count unique items in field in Access query?
  80. Sqlite: CURRENT_TIMESTAMP is in GMT, not the timezone of the machine
  81. MySQL LIKE IN()?
  82. Filter data based on date in sql
  83. ora-06553 pls-306 wrong number or types of arguments in call to ‘ogc_x’
  84. SQL query to extract only the “current” wp_posts?
  85. How to retrieve sticky post in raw sql?
  86. How to remove in the wordpress database all posts revisions except the last three?
  87. How do I move a WordPress site to another server?
  88. Backticks (`) Instead of Single Quotes (‘) in an SQL Statement?
  89. $wpdb get_var issue
  90. WP Optimization: Removing Orphaned wp_options (especially the autoload ones)
  91. Auto-update tables from database
  92. Inserting rows into a custom table, when plugin is activated
  93. dbDelta not creating table (yes, once more)
  94. Posts modified in the last 48 hours
  95. post id not displaying
  96. How to export 2 week’s worth of posts
  97. Insert multiple checkbox values
  98. How to get EVENT based on startday, using BETWEEN
  99. wpdb prepare without placeholder
  100. GRANT SELECT to all tables in postgresql

Leave a Comment