CODEWITHSUNDEEP
sqloracle-database
user273870
user273870

Reputation: 5

Average balance query in Oracle SQL

The question is: List the average balance of customers by city. Only include customers residing in Washington state (‘WA’)

my code is 

SELECT DISTINCT CUSTCITY, AvgBal
FROM
  (SELECT AVG(CUSTBAL) AvgBal FROM RCHAMART.CUSTOMER),
 RCHAMART.CUSTOMER
WHERE CUSTSTATE='WA';

the results look like 

Renton  351.3125
Lynnwood    351.3125
Seattle 351.3125
Monroe  351.3125
Bellevue    351.3125
Fife    351.3125

The problem I am having is that it is showing me the average balance for every city combined next to each city. Instead of showing the average for just that city.

Upvotes: 0

Views: 800

Answers (1)

GriffeyDog
GriffeyDog

Reputation: 8376

Use a group by:

select custcity, avg(custbal) as AvgBal
from rchamart.customer
where custstate = 'WA'
group by custcity;

Upvotes: 5

Related Questions