What I learned today

As part of a Rails project I had to gather up some data from a single table, and calculate various stats on the data Problem was - had to group data by a field, then calculate aggregates in various ways not possible with a single GROUP BY query Using the SQL cookbook tips, and some pondering, I learned that I could create a bunch of 'fake' tables from the same table using subqueries, then join these tables in a way that would yield a single resultset An example: Let us say you have a table of person, their test points whether they passed the test attempt, like (two people a and b tried a test at various times) : Name Points Passed a 10 1 a 25 0 b 21 1 a 28 0 b 23 1 a 35 1 b 31 1 a 35 1 b 41 1 You want a result like Name Avg Age No. of attempts'passed' a 30.3 5 b 25.1 4 (caution: These numbers are probably wrong just too lazy to fix them up!) As you can see, it is not possible with a single query, since grouping by name and