Chapter 5 Try it yourself

[katemartin9]

Hi,

I was doing Task 3 from Chapter 5 Try it Yourself. And I wanted to try and find a median value for all of the states. However, my code doesn't seem to be working when I try to add the geo_name column in the select function (works well without it).

Would you be able to help me amend the SQL statement so that it brings back the medians of each state with the county name next to it?

SELECT DISTINCT state_us_abbreviation, geo_name,
percentile_cont(0.5)
WITHIN GROUP (ORDER BY p0010001) AS "pct"
from us_counties_2010
GROUP BY 1, 2; 

[anthonydb]

Hi, @katemartin9 --

If you want to find the median for each state at once, you can write a query like this:

SELECT state_us_abbreviation,
       percentile_cont(0.5)
          WITHIN GROUP (ORDER BY p0010001) AS median
FROM us_counties_2010
GROUP BY state_us_abbreviation;

I wasn't sure from your question if you're additionally looking to identify a county in each state that is at the median value. That's a little more complicated, but it can be done if you use percentile_disc instead of percentile_cont and do a join on tables (which I cover in Chapter 6):

SELECT * 
FROM 
    (SELECT state_us_abbreviation,
           percentile_disc(0.5)
               WITHIN GROUP (ORDER BY p0010001) AS median
    FROM us_counties_2010
    GROUP BY state_us_abbreviation) AS median
LEFT JOIN
    (SELECT state_us_abbreviation,
            geo_name,
            p0010001
     FROM us_counties_2010) AS counties
ON median.state_us_abbreviation = counties.state_us_abbreviation
   AND median.median = counties.p0010001
ORDER BY median.state_us_abbreviation;

In that example, we're writing two queries and joining them. The first finds the median in each state, and the second query lists all the counties. We're then finding the values that match the calculated median.

I hope that helps. If you still have a question, please let me know.

[anthonydb]

Closing this but please reach out if you have any other questions, @katemartin9