BigQuery: Best way to ORDER BY an arbitrary GROUP BY expression when that expression is not in the SELECT clause?

user_976126 0
Viewed 293

In the below query, I want to group all orders by the concatenation of firstname and lastname, and order the result set by that concatenation. I do not want to include the concatenation in the SELECT column list. BigQuery returns the following error for the query:

SELECT 
    COUNT(o.ORDERID)
FROM EMPLOYEES e
INNER JOIN ORDERS o ON e.EMPLOYEEID = o.EMPLOYEEID
GROUP BY CONCAT(e.FIRSTNAME, ' ', e.LASTNAME)
ORDER BY CONCAT(e.FIRSTNAME, ' ', e.LASTNAME)

ORDER BY clause expression references column [column_name] which is neither grouped nor aggregated

It seems to me that the firstname/lastname concatenation is necessarily unique per group, so one should be able to include it in the ORDER BY clause.

I can fix the error by wrapping the ORDER BY clause in ANY_VALUE:

SELECT 
    COUNT(o.ORDERID)
FROM EMPLOYEES e
INNER JOIN ORDERS o ON e.EMPLOYEEID = o.EMPLOYEEID
GROUP BY CONCAT(e.FIRSTNAME, ' ', e.LASTNAME)
ORDER BY ANY_VALUE(CONCAT(e.FIRSTNAME, ' ', e.LASTNAME))

Is this best practice? Is there a more canonical way to do this for arbitrary GROUP BY expressions? Other DBMS, including MySQL, SQLServer, Postgres, etc. handle the first query without error.

google-bigquery sql
2 Answers

how about using sub-query?

select OrderCount from (
    SELECT CONCAT(e.FIRSTNAME, ' ', e.LASTNAME) as fullname
        ,COUNT(o.ORDERID) as OrderCount
    FROM EMPLOYEES e
    INNER JOIN ORDERS o ON e.EMPLOYEEID = o.EMPLOYEEID
    GROUP BY fullname
    ORDER BY fullname
) t
user_13674540

Your method is actually fine, although I usually use MIN() or MAX() just because that is common across all SQL dialects.

However, here is a trick for defining new column aliases in the FROM clause, which then allows you to use them in the GROUP BY and ORDER BY:

SELECT COUNT(o.ORDERID)
FROM EMPLOYEES e INNER JOIN
     ORDERS o
     ON e.EMPLOYEEID = o.EMPLOYEEID CROSS JOIN
     UNNEST(ARRAY[CONCAT(e.FIRSTNAME, ' ', e.LASTNAME)]) fullname
GROUP BY fullname
ORDER BY fullname;

You could also use a correlated subquery:

select (select count(*)
        from orders o
        where o.employeeid = e.employeeid
       )
from employees e
order by CONCAT(e.FIRSTNAME, ' ', e.LASTNAME)

Note: This returns 0 values as well.

user_11440350
Related