After the April 28, 2018 webcast, one of our key developers, Eric Hill answered some questions that came up at the webcast but which Chris did not have time to address. Here are the Query Builder questions and Eric's responses:
Question: When in the initial Query Builder window where you can join tables, can you join between secondary tables?
Answer: Absolutely. Any table in the query can be joined to any other.
Question: Can I join two tables if key/column is numeric in one table and character in the other?
Answer: For some databases, join columns with mismatched types are coerced to be the same type, but others not. If you have this situation, the best thing to do is drop into custom SQL and cast the string to a number. Though this StackOverflow article suggests that SQL will just do the conversions on your behalf.
Question: When one chooses to aggregate using Average, how is that done? For example, in the Movie Rental example, when we take average age, is that averaging across unique customers or looking at all the records within the movie information that defines a group? The concern is that if a customer is renting multiple times the same movie, they might get counted multiple times and thus the sample size would be inflated. I suspect this might be dependent on the DB type.
Answer: The database is going to average across whatever rows are in each group. The best thing to do is to make the query you want without aggregating, making sure if you only want each customer to be present one time that that’s what you’ve got, and then apply the aggregation. It may require custom SQL or doing one query that gets the rows you want and then a JMP query against the result that does the aggregation.