Question regarding code error

My Code:

I get an error if I run:

SELECT
e.first_name || ' ' || e.last_name as employee_name,
COUNT(c.customer_id) customer_gt_90_dollars
FROM employee e LEFT JOIN customers_usa_gt_90 c
ON e.employee_id = c.support_rep_id
WHERE e.title = "Sales Support Agent"
GROUP BY employee_name, ORDER by employee_name;


BUT

If I run below - it works. (only difference is in group by / order by)

SELECT
e.first_name || ' ' || e.last_name as employee_name,
COUNT(c.customer_id) customer_gt_90_dollars
FROM employee e LEFT JOIN customers_usa_gt_90 c
ON e.employee_id = c.support_rep_id
WHERE e.title = "Sales Support Agent"
GROUP BY 1, ORDER BY 1;
Error ((sqlite3.OperationalError) near "ORDER": syntax error
[SQL: with customers_usa_gt_90 AS (SELECT * FROM customer_gt_90_dollars INTERSECT SELECT * FROM customer_usa)  SELECT e.first_name || ' ' || e.last_name as employee_name, COUNT(c.customer_id) customer_gt_90_dollars FROM employee e LEFT JOIN customers_usa_gt_90 c ON e.employee_id = c.support_rep_id WHERE e.title = "Sales Support Agent" GROUP BY employee_name, ORDER by employee_name;]
(Background on this error at: http://sqlalche.me/e/13/e3q8))

WHY? in the first code I am naming the group by order by column by name vs in the 2nd code I am mentioning the column # relative to the select statement.

Thanks!

Please make sure to include the link to the lesson/screen corresponding to your question and code so that others have the appropriate context (such as being able to run your code) to help you out.

I would say that both would produce an error because you can’t use GROUP BY something, ORDER BY something. They are separate clauses without any comma between them. Because, right now, the ORDER BY statement is being treated as part of the GROUP BY one because of that comma and thus producing an error.

If you only get an error once with the exact code that you have shared, then sharing the link to the lesson screen would help to be able to run and test it.

2 Likes