SQLite Forum

Using an alias in queries
Login
Apologies for the confusion, and thank you for taking some time with this. CL is Command Line. 

schema:
<code>
CREATE TABLE sqlite_sequence(name,seq);
CREATE TABLE IF NOT EXISTS "Keywords" (
	`id`	INTEGER,
	`authorid`	INTEGER,
	`bookid`	INTEGER,
	`keyword`	TEXT,
	PRIMARY KEY(`id` AUTOINCREMENT)
);
CREATE TABLE `test` (
	`text`	TEXT
);
CREATE TABLE IF NOT EXISTS "Books" (
	`id`	INTEGER,
	`authorid`	INTEGER,
	`bookid`	INTEGER,
	`pubyear`	INTEGER,
	`title`	TEXT,
	`notes`	TEXT,
	`author`	TEXT,
	PRIMARY KEY(`id` AUTOINCREMENT)
);
CREATE TABLE IF NOT EXISTS "Authors" (
	`id`	INTEGER,
	`authorid`	INTEGER NOT NULL,
	`author`	TEXT NOT NULL,
	PRIMARY KEY(`id` AUTOINCREMENT)
);
</code>

Here is a typical bit of the Keywords table:
<code>
id    authorid  bookid  keyword                 
----  --------  ------  ------------------------
2122  1         330     Mathematics
2123  1         331     Mathematics
2124  1         323     Philosophy
2125  2         404     Knots
2126  5         82      Document Design
2127  5         152     Web Design
2128  6         550     Scalable Vector Graphics
2129  7         179     Physics  
2130  7         178     Quantum Theory
2131  8         294     bash
</code>

A CL version of the code:
<code>
@SubColors=("red","orange","yellow","green","blue");
$ndx=0;

$sth=$dbh->prepare(qq{ select count(keyword) as NUM,keyword from keywords group by keyword order by count(keyword) desc limit 5;});
$sth->execute();
while (@records=$sth->fetchrow_array()) {
	print qq{records[0] $records[1] $SubColors[$ndx]\n};
	$NumKWBooks+=$records[0];
	$KeyWords{keyword}	= $records[1]; # dynamic hash
	$KeyWords{numbooks}	= $records[0], # dynamic hash
	$ndx+=1;
}
$sth->finish();

print qq{\$NumKWBooks: $NumKWBooks\n};
my $numKeys=keys %KeyWords;
print "\$numKeys: $numKeys\n";
foreach $key(keys %KeyWords) {
        $percentKW=$KeyWords{numbooks} / $NumKWBooks * 100;
	print qq{$KeyWords{keyword}: $tab };
	printf "%.2f%%\n",$percentKW;
}
</code>

which results in:
<code>
records[0] Programming red
records[0] Mathematics orange
records[0] Perl yellow
records[0] Knots green
records[0] Calculus blue
$NumKWBooks: 5
$numKeys: 2
Calculus:      20.00%
Calculus:      20.00%
</code>

As you can see $records[0] is not getting populated since it is an alias in the query loop. Also the number of keywords in the 5 chosen is wrong - it should be over 100.

This is the bit that is crucial for my purposes since I am using these values to build an SVG donut chart of the top 5 keywords in this library. No joy so far.