Can this be a Nested Query

Question

What are the names and length of the shoreline, in order of descending shoreline in the US, of the Great Lakes that have shorelines in both Canada and the US.

The table I use has three columns, IN_COUNTRY(where the lakes are, US or Canada), ON_LAKE(the name of the lake, like Lake Michigan) and SHORELINE(length of shoreline in each country)

I have tried the SQL listed below but I can not order it by the SHORELINE length in the United States. As you can see, I can now only order them by the combination of the shoreline in both countries.

SELECT ON_LAKE, sum(SHORELINE) AS SHORELINE
FROM SHORE
GROUP BY ON_LAKE
HAVING count(IN_COUNTRY) > 1
ORDER BY SHORELINE DESC

I think the nested SQL may help but has been stuck here for a while

Image:

Answer 1

If you want to use an aggregation approach, then we can try this:

SELECT ON_LAKE, SUM(SHORELINE) AS SHORELINE
FROM SHORE
GROUP BY ON_LAKE
HAVING MIN(IN_COUNTRY) = 'Canada' AND MAX(IN_COUNTRY) = 'United States'
ORDER BY
    SUM(CASE WHEN IN_COUNTRY = 'United States' THEN SHORELINE ELSE 0 END) DESC;

Demo

Answer 2

Try something like this:

SELECT ON_LAKE, sum(SHORELINE) AS SHORELINE
FROM SHORE
GROUP BY ON_LAKE
HAVING count(IN_COUNTRY) > 1
ORDER BY sum(CASE WHEN IN_COUNTRY='United States' THEN SHORELINE ELSE 0 END) DESC

I only changed the ORDER BY clause to calculate the shore length in United States.

Answer 3

Try this ,

SELECT ON_LAKE,  sum(SHORELINE)
FROM SHORE
where IN_COUNTRY='Canada' and IN_COUNTRY='United States'
Group By ON_LAKE
ORDER BY sum(SHORELINE) DESC