1

I have a single sqlite table that I'm trying to pull two resultsets from and then calculate the percentage. Table columns:

  • email
  • Location
  • Clicked?
  • Test#

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?

2
  • 1
    Please post DDL for the table and some sample data. Also, which RDBMS? – zgguy Dec 4 '15 at 19:54
  • @zgguy sqlite, as specified in the title of the question – Philᵀᴹ Dec 5 '15 at 16:19
1

A fellow redditor provided me with the assistance to answer my question. The following is the end result that did what I needed:

select t.location, printf("%.3f", sum(st.456Clicks))/printf("%.3f", sum(st.123Clicks)) as percentage
from (
    select email, count(*) as 123Clicks, 0 as 456Clicks
    from table
    where test = "123"
    group by email

    union all

    select email, 0 as 123Clicks, count(*) as 456Clicks
    from table
    where test = "456" and "Clicked?" = "Yes"
    group by email
) st
join table t
on t.email = st.email
where t."Clicked?" = "Yes" and t.test = "123"
group by t.location
order by percentage DESC
1
  • It won't let me accept it until tomorrow, day after I posted it. – user2434258 Dec 5 '15 at 20:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.