I've been playing with BigQuery public data sets, i.e. bigquery-public-data.geo_international_ports.world_port_index.

I'm selecting 'country' and 'harbor_size' columns and I'm calculating total count of country. I'm adding this as new row with value 'ALL' in column 'harbor_size'. I want to sort on this total count but in a way, where all country elements stay together. Original 'harbor_size' elements: ['S', 'M', 'L', 'V'].

Here's what I have so far: enter image description here

Query: 

    WITH
      total_n AS 
      (   
        SELECT     
          country,     
          "ALL" AS harbor_size,     
          COUNT(country) as hb_count   
        FROM bigquery-public-data.geo_international_ports.world_port_index
        GROUP BY country 
        ORDER BY country ASC, hb_count DESC
      )
    SELECT * FROM total_n 
    
    UNION ALL
    
    SELECT 
      country, 
      harbor_size, 
      COUNT(country) as hb_count 
    FROM bigquery-public-data.geo_international_ports.world_port_index
    GROUP BY country, harbor_size 
    ORDER BY country ASC, hb_count DESC;

So in the end, I'd like to have sth. like this: 

| country | h_size | count  | remark          |
|---------|--------|--------|-----------------|
| US      | ALL    | 10,000 | <- sort on this |
| US      | L      |  7,000 |                 |
| US      | M      |  2,000 |                 |
| US      | S      |  1,000 |                 |
| CA      | ALL    |  9,000 | <- sort on this |
| CA      | L      |  6,000 |                 |
| CA      | M      |  2,000 |                 |
| CA      | S      |  1,000 |                 |
| ...     | ...    |    ... | ...             |


I was considering having temp table with only 'ALL' in 'harbor_size' then adding index after sorting, so that each country would have an index, then unioning this with the other table (country, harbor_size w/ all elements, count) and sorting on the index.