Non-Corrolated Subqueries III


#1

https://www.codecademy.com/courses/sql-table-transformation/lessons/subqueries/exercises/non-correlated-iii


SELECT a.dep_month,
       a.dep_day_of_week,
       AVG(a.flight_distance) AS average_distance
  FROM (
        SELECT dep_month,
              dep_day_of_week,
               dep_date,
               sum(distance) AS flight_distance
          FROM flights
         GROUP BY 1,2,3
       ) a
 GROUP BY 1,2
 ORDER BY 1,2;

So my code is correct, but I only got it by getting the wrong answer repeatedly and then finally getting the "get code" option. I have a lot more experience in Java, JavaScript, and Python, but I just did the first SQL course this morning and since they give you the code to every single exercise, it was a lot easier, and more just a basic explanation. I took notes during this but now that I'm attempting all of this stuff on my own, I'm having a lot more trouble. This code makes no sense to me really and I wasn't sure if somebody could break it down for me so that I can use the knowledge in later lessons?
Thanks! :slight_smile:


#2

In no way will I presume to answer your question as I have one or two of my own...

Let's first look at the unseen subquery table (slightly embellished)...

select dep_month,
       dep_day_of_week,
       dep_date,
       distance,
       sum(distance) as flight_distance
from flights
group by 1,2,3,4
order by 1,4;

Which reports,

dep_month  dep_day_of_week  dep_date   distance  flight_distance

2000-01    Wednesday        1/12/2000  280       280
2000-01    Saturday         1/1/2000   328       328
2000-01    Sunday           1/23/2000  335       335
2000-01    Sunday           1/9/2000   696       696
2000-01    Sunday           1/10/2000  1043      1043
2000-01    Thursday         1/21/2000  1045      1045
2000-01    Monday           1/31/2000  1302      1302
2000-01    Tuesday          1/11/2000  1372      1372
2000-01    Tuesday          1/18/2000  1448      1448
2000-01    Thursday         1/13/2000  2565      2565
... (644 rows)

My question arises from the similarity of distance and sum(distance). Someone will need to explain this discrepancy. What is being summed up? All the distances of Monday flights in January? I don't see how.

Another question arises from the AVG() function not being given a COUNT value to work with. Where is that hidden?


#3

Well the average is given by the inner query of flight_distance, right? So the inner query, flight_distance, has to be providing the sum of the counts by day of week? month??


#4

Please don't get me wrong... I'm not asking you, per se. This is not a critique on your code, but the solution code given to you. We're both scratching our heads, I'm afraid.

That's my question, as well, as in count of flights divided into total of distances. I don't see where that is happening in the provided solution given by Get Code, and hope that someone will weigh in here who can clarify this.


#5

Oh, okay! Me too, for sure. The example is also somewhat elusive. It almost seems like the code is almost missing things. I've seen other posts in this same category of people asking for explanations yet I haven't found a helpful one.


#6

Found this on W3resource.com...

http://www.w3resource.com/sqlite/sqlite-subqueries.php


#7

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.