Error Code: 1055. Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column

Question

select c.Cust_id, Customer_Name, round(sum(Sales)) as Total_Sales
  from market_fact_full m
 inner join cust_dimen c using (Cust_id)
 group by c.Cust_id
 order by Sales desc;

Also when I add all non aggregated cols I still get the error:

select c.Cust_id, Customer_Name, round(sum(Sales)) as Total_Sales
  from market_fact_full m
 inner join cust_dimen c using (Cust_id)
 group by c.Cust_id, Customer_Name
 order by Sales desc;

Error Code: 1055. Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'market_star_schema.m.Sales' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Answer 1

That's because you are trying to order by column sales - but since you are grouping by customer, there are multiple sales in each group, so the database can't quite tell what it should do.

Presumably, you want to order customers by their total sales. For this, you can refer to the alias defined in the from clause:

select c.cust_id, c.customer_name, round(sum(m.sales)) as total_sales 
from market_fact_full m 
inner join cust_dimen c using (Cust_id) 
group by c.cust_id, c.customer_name 
order by total_sales desc;