MySQL: Find the average balance of customers based on State

Question

Find the average balance of customers based on State

select * from Customer_info;
select * from Account_info;

select Customer_info.Customer_ID, Customer_info.State, Account_info.Balance, Account_info.Customer_ID
from Customer_info
inner join Account_info on Customer_info.Customer_ID = Account_info.Customer_ID;

select Customer_info.Customer_ID, Customer_info.State, avg(Balance) as Average_Bal
from Customer_info
inner join Account_info on Customer_info.Customer_ID = Account_info.Customer_ID
group by State;

Stuck, could use some help/suggestions.

Answer 1

You seem to want:

select c.state, avg(a.balance) as average_bal
from customer_info c
inner join account_info a on a.customer_id = c.customer_id
group by c.state;

The select clause needs to be consistent with the group by clause: all non-aggregated column must appear in the group by clause.

Note that I added table aliases, which shortens the query. I also prefixed column balance with the table it belongs to, so the query is unambiguous (I assumed account_info).