In Presto query is it possible to refer column name rathen then the whole syntax

Question

This is my query

SELECT regexp_replace(url_extract_path(request_url), '(.*)\/([\d]+)$', '$1') as Path, 
COUNT(regexp_replace(url_extract_path(request_url), '(.*)\/([\d]+)$', '$1')) as countreq
FROM "tk_logs"."services-june" 
WHERE url_extract_host(request_url) IN ('website1.com','website2.com')
GROUP BY regexp_replace(url_extract_path(request_url), '(.*)\/([\d]+)$', '$1')
ORDER BY count(regexp_replace(url_extract_path(request_url), '(.*)\/([\d]+)$', '$1')) desc 

When I run same query like

SELECT regexp_replace(url_extract_path(request_url), '(.*)\/([\d]+)$', '$1') as Path, 
COUNT(regexp_replace(url_extract_path(request_url), '(.*)\/([\d]+)$', '$1')) as countreq
FROM "tk_logs"."services-june" 
WHERE url_extract_host(request_url) IN ('website1.com','website2.com')
GROUP BY Path
ORDER BY countreq desc 

I get error

Column 'Path' cannot be resolved

Usually in mysql query this is allowed. For Presto am I supposed to do it some other way?

Sample MySql query

SELECT COUNT(CustomerID) as cust_count, Country
FROM Customers
GROUP BY Country
ORDER BY cust_count;

Answer 1

No, you can't, you need either do subselect:

SELECT ...
FROM (
    SELECT regexp_replace(url_extract_path(request_url), '(.*)\/([\d]+)$', '$1') as Path, 
        regexp_replace(url_extract_path(request_url), '(.*)\/([\d]+)$', '$1') as countreq
    FROM "tk_logs"."services-june" 
    WHERE url_extract_host(request_url) IN ('website1.com','website2.com')
)
GROUP BY Path
ORDER BY count(countreq)

Or refer them by index, which is considered not a best practice though:

SELECT regexp_replace(url_extract_path(request_url), '(.*)\/([\d]+)$', '$1') as Path, 
COUNT(regexp_replace(url_extract_path(request_url), '(.*)\/([\d]+)$', '$1')) as countreq
FROM "tk_logs"."services-june" 
WHERE url_extract_host(request_url) IN ('website1.com','website2.com')
GROUP BY 1
ORDER BY 2 desc 

P.S.

Note that unless you are doing distinct count COUNT(regexp_replace(url_extract_path(request_url), '(.*)\/([\d]+)$', '$1')) as countreq should not differ from count(request_url).