Hi this is my homework and the question is this: List the average balance of customers by city and short zip code (the first five digits of thezip code). Only include customers residing in Washington State (‘WA’). also the Customer table has 5 columns(Name,Family,CustZip,CustCity,CustAVGBal)
I wrote the query like below is this correct?
SELECT CustCity,LEFT(CustZip,5) AS NewCustZip,CustAVGBal
FROM Customer
WHERE CustCity = 'WA'
THANKS!!
No. Because you're truncating the zip code, you'll have many records that are duplicates. Your query needs to account for this and aggregate those into a single record. Also, you need a way to get the state from the zip code (are we missing another table). It's possible that you've omitted a column in your question -- if you have the state in the table, use it to select on.
No, that is not correct. You're asked to limit the people in the query by state, not city. To make the problem a little more interesting, there's no state column in the Customer table, so you're going to have to figure out how to limit the records without referring directly to the state.
Can you think of any ways to do this?
Your question isn't very clear "by city and short zip code". Depending on what that means exactly you might need to look at "group by" or "order by".
I think the assignment is expecting you to list a single average per city/short-code combination. You'll need to employ the GROUP BY clause and the AVG function.
Also, CustCity will never be 'WA'; you probably have to derive the 'WA' check from the zip code (I don't live in the U.S., but I guess that looking at the first two digits will suffice).
Your query isn't finding the average, it would return multiple rows for the same CustCity and NewCustZip. Look at the AVG function as well as the GROUP BY clause.
Also, the city's not going to be Washington. You probably need to get a list of all the zip-code prefixes for Washington and check for them in your query. Look here.