When working with a pie chart, Caravel produced data like this:
SELECT domain AS domain, COUNT(_) AS cnt
FROM am_joined INNER JOIN (SELECT domain AS __domain
FROM amjoined GROUP BY domain ORDER BY COUNT() DESC
LIMIT 10) AS anon_1 ON domain = __domain GROUP BY domain ORDER BY cnt DESC
LIMIT 10
(Note: I added the Limits... see the other issue about limits)
This didn't work, Drill produces a VALIDATION ERROR: From line 4, column 33 to line 4, column 42: Column '__domain' is ambiguous
Instead:
SELECT domain AS domain, COUNT(_) AS cnt
FROM am_joined INNER JOIN (SELECT domain AS domain
FROM amjoined GROUP BY domain ORDER BY COUNT() DESC
LIMIT 10) AS anon_1 ON anon_1.domain = domain GROUP BY domain ORDER BY cnt DESC
LIMIT 10
Needs to be put in. Basically the subquery is "as anon_1" and whatever that qualifier is needs to be on the JOIN so that drill doesn't get confused.
When working with a pie chart, Caravel produced data like this:
SELECT domain AS domain, COUNT(_) AS cnt FROM am_joined INNER JOIN (SELECT domain AS __domain FROM amjoined GROUP BY domain ORDER BY COUNT() DESC LIMIT 10) AS anon_1 ON domain = __domain GROUP BY domain ORDER BY cnt DESC LIMIT 10
(Note: I added the Limits... see the other issue about limits)
This didn't work, Drill produces a VALIDATION ERROR: From line 4, column 33 to line 4, column 42: Column '__domain' is ambiguous
Instead: SELECT domain AS domain, COUNT(_) AS cnt FROM am_joined INNER JOIN (SELECT domain AS domain FROM amjoined GROUP BY domain ORDER BY COUNT() DESC LIMIT 10) AS anon_1 ON anon_1.domain = domain GROUP BY domain ORDER BY cnt DESC LIMIT 10
Needs to be put in. Basically the subquery is "as anon_1" and whatever that qualifier is needs to be on the JOIN so that drill doesn't get confused.