Blog roll
R-bloggers, Debian Weekly![[Valid RSS]](valid-rss-rogers.png "Validate my RSS feed")
Last modified:
2007-03-10
Problem to be solved: get statistics rather than lists of matchings posts using only SQL.
Sometimes it is easier to get what you want using a mixture of languages. In this article I will use such an example, where using perl and making several SQL-queries would have been easier than a pure SQL solution. But being able to express what you want in pure SQL gives other advantages (other than being simple to program, that is), eg. using a system that takes sql-queries and produces a report of the tables that these sql-queries return. A solution that mixed perl and SQL would have had to some other hacks to fit into that system.
Limiting one self to a particular programming languague, or a particular old operating system or even a particular old computer can also be a fun challenge.
The kind of output I want is a cross-table of percentages like this one:
Sex Not approved (%) Approved (%) Excellence (%) Number of students Woman 1 68 31 304 Man 2 70 28 43
To acheive this, use a outer loop through a table of sexes (with two values, "Woman" and "Man")
select sex [...] from sexes;
This main loop creates the rows. Then for each column you want, you have to create a compound expression within parentheses and name the column (In the example above, the first column was named by adding ' as "Not approved (%)"' after the parentheses). In the compound expressions the sql function "count" is used.
But what about looping through some property for which there is no table in the database? As an example I wanted to produce statistics about the relation between the number of authors and grades for their essays, but the number of authors where not recorded in the database. I had an algorithm to calculate the number of authors using data in the database.
In this case, you have to create a virtual table and refer to that table to get the rows you want.