I have a single sqlite table that I'm trying to pull two resultsets from and then calculate the percentage. Table columns:
Test# 123 has a list of users (identified by email address) who clicked a certain link, Test# 456 includes the same people who clicked in test#123, but with only a subset of the users from Test# 123 clicking a second time. The goal is to pull the percentage of those who clicked a second time in Test#456.
Query 1: (dividend) to calculate those who clicked a second time is:
SELECT Location, count(*) as "rClicks" from table WHERE Test# = "456" and "Clicked?" = "Yes" and email IN ( SELECT email from table WHERE test# = "123" and "Clicked?" = "Yes") GROUP BY Location
Query 2: (divisor) to calculate the total who clicked on the first test is:
SELECT Location, count(*) as "Clicks" from table WHERE test# = "123" and "Clicked?" = "Yes" GROUP BY Location
I've tried all sorts of combinations, the most promising being an INNER JOIN on the two result sets, but I keep getting 0.0 for the percentage.
SELECT query1.Location, query1."rClicks" / query2."Clicks" * 100.0 as "Percentage" FROM (query 1) repeats INNER JOIN (query 2) previous ON repeats.Location = previous.Location
Any suggestions for getting this right?