count
COUNT(id) vs. COUNT(*) in MySQL
I know this question is about MySQL, but for what it’s worth, count(*) is recommended for Oracle, which goes to show that the answer to this can be database dependent (see comment above from BalusC). Since a lot of databases (MS-SQL, MySQL) have information schema tables that hold various types of metadata, there are bound … Read more
Limiting results in MongoDB but still getting the full count?
By default, count() ignores limit() and counts the results in the entire query. So when you for example do this, var a = db.collection.find(…).limit(10); running a.count() will give you the total count of your query.
How do I add two count(*) results together on two different tables?
Wrap them up and use subqueries: SELECT (SELECT COUNT(*) FROM Toys WHERE little_kid_id = 900)+ (SELECT COUNT(*) from Games WHERE little_kid1 = 900 OR little_kid2 = 900 OR little_kid3 = 900) AS SumCount Voila!
How to count identical string elements in a Ruby array
Ruby v2.7+ (latest) As of ruby v2.7.0 (released December 2019), the core language now includes Enumerable#tally – a new method, designed specifically for this problem: names = [“Jason”, “Jason”, “Teresa”, “Judah”, “Michelle”, “Judah”, “Judah”, “Allison”] names.tally #=> {“Jason”=>2, “Teresa”=>1, “Judah”=>3, “Michelle”=>1, “Allison”=>1} Ruby v2.4+ (currently supported, but older) The following code was not possible in … Read more
Laravel Eloquent – distinct() and count() not working properly together
The following should work $ad->getcodes()->distinct()->count(‘pid’);
Run a query with a LIMIT/OFFSET and also get the total number of rows
Yes. With a simple window function: SELECT *, count(*) OVER() AS full_count FROM tbl WHERE /* whatever */ ORDER BY col1 OFFSET ? LIMIT ? Be aware that the cost will be substantially higher than without the total number, but typically still cheaper than two separate queries. Postgres has to actually count all rows either … Read more
How to find duplicate elements in array in effective way? I mean to say with very less iterations
You can use a HashSet because Sets don’t allow duplicates, just loop over array of elements and insert them into a HashSet using the add() method. If the add() method returns back false then that element already exists in the set and it is there for your duplicate. This way you only loop over the … Read more
PGError: ERROR: aggregates not allowed in WHERE clause on a AR query of an object and its has_many objects
The error message tells you: aggregates not allowed in WHERE clause count() is an aggregate function. Use the HAVING clause for that. The query could look like this: SELECT r.* FROM recommendations r JOIN approvals a ON a.recommendation_id = r.id WHERE r.user_id = $current_user_id GROUP BY r.id HAVING count(a.recommendation_id) = 1 With PostgreSQL 9.1 or … Read more
Using SQlite3 in PHP how to count the number of rows in a result set?
$db = new SQLite3(‘filename.db3’); $count = $db->querySingle(“SELECT COUNT(*) as count FROM tablename”); echo $count;