All columns in the SELECT clause that do not have an aggregate need to be in the GROUP BY Good: SELECT col1, col2, col3, MAX(col4) … GROUP BY col1, col2, col3 Also good: SELECT col1, col2, col3, MAX(col4) … GROUP BY col1, col2, col3, col5, col6 No other columns = no GROUP BY needed SELECT … Read more
The exact query can be produced using the tuple_() construct: session.query( func.count(distinct(tuple_(Hit.ip_address, Hit.user_agent)))).scalar()
The easiest way is to not store the count, relying on the COUNT aggregate function to reflect the value as it is in the database: SELECT c.category_name, COUNT(p.post_id) AS num_posts FROM CATEGORY c LEFT JOIN POSTS p ON p.category_id = c.category_id You can create a view to house the query mentioned above, so you can … Read more
Probably not the most elegant of solutions, and the performance of IN may suffer on larger tables. The nested query gets the minimum Birthyear for each city. Only records who have this Birthyear are matched in the outer query. Ordering by age then limiting to 3 results gets you the 3 oldest people who are … Read more
SELECT id, SUM(amount) AS amount FROM yourtable GROUP BY id
In SWI-Prolog: aggregate_all(count, is_man(X), Count).
You’re looking for GROUP_CONCAT() Try this: select group_concat(MyString separator ‘, ‘) as myList from table where id < 4 Of course, you can group by the results.
SELECT Country, COUNT(*) AS Total, COUNT(CASE WHEN Reconnect = true THEN 1 END) AS With_Reconnect FROM PlayerSession S LEFT JOIN Player P ON P.id = S.player_id GROUP BY country
The reason you can’t use SUM() in the WHERE clause is the order of evaluation of clauses. FROM tells you where to read rows from. Right as rows are read from disk to memory, they are checked for the WHERE conditions. (Actually in many cases rows that fail the WHERE clause will not even be … Read more
Let’s see if some ascii art doesn’t help. Consider the type signature of aggregate: def aggregate [B] (z: B)(seqop: (B, A) ⇒ B, combop: (B, B) ⇒ B): B Also, note that A refers to the type of the collection. So, let’s say we have 4 elements in this collection, then aggregate might work like … Read more