Merge pull request swcarpentry#315 from danmichaelo/ep6-sol

Ep 6: Add solution to exercise "What Does This Query Do?"
tanash1983 · Mar 10, 2020 · f01373b · f01373b
2 parents 9fcec77 + 195b9f1
commit f01373b
Showing 1 changed file with 33 additions and 1 deletion.
diff --git a/_episodes/06-agg.md b/_episodes/06-agg.md
@@ -376,7 +376,39 @@ this query:
 > ~~~
 > {: .sql}
 >
-> What does this actually produce, and why?
+> What does this actually produce, and can you think of why?
+>
+> > ## Solution
+> > The query produces only one row of results when we what we really want is a result for each of the readings.
+> > The `avg()` function produces only a single value, and because it is run first, the table is reduced to a single row.
+> > The `reading` value is simply an arbitraty one.
+> >
+> > To achieve what we wanted, we would have to run two queries:
+> >
+> > ~~~
+> > SELECT avg(reading) FROM Survey WHERE quant='rad';
+> > ~~~
+> > {: .sql}
+> >
+> > This produces the average value (6.5625), which we can then insert into a second query:
+> >
+> > ~~~
+> > SELECT reading - 6.5625 FROM Survey WHERE quant = 'rad';
+> > ~~~
+> > {: .sql}
+> >
+> > This produces what we want, but we can combine this into a single query using subqueries.
+> >
+> > ~~~
+> > SELECT reading - (SELECT avg(reading) FROM Survey WHERE quant='rad') FROM Survey WHERE quant = 'rad';
+> > ~~~
+> > {: .sql}
+> >
+> > This way we don't have execute two queries.
+> >
+> > In summary what we have done is to replace `avg(reading)` with `(SELECT avg(reading) FROM Survey WHERE quant='rad')` in the original query.
+> >
+> {: .solution}
 {: .challenge}
 
 > ## Ordering When Concatenating