SQL Question

[ivalum21]

I need to run a query that will take the average balances of customers by city. So for example, I would need the average balance for all customers from Chicago. So I would display one row for Chicago with the average balance of all customers from Chicago.

I try using the DISTINCT function along with the AVG function, but I get a weird error.

Someone help!!

 

[r937]

probably forgot the GROUP BY clause:

select city
     , avg(balance) as avgbal
  from yourtable
group
    by city

if you specify the city in the WHERE clause, then you can leave it out of the SELECT list, and therefore you can omit the GROUP BY:

select avg(balance) as avgbal
  from yourtable
 where city='Chicago'

in other words, you can omit the GROUP BY only if there are no non-aggregates (columns) in the SELECT list

stated another way, you can omit the GROUP BY only if the SELECT list contains only aggregates

rudy

http://r937.com/

SQL Consulting

 

[TruthInSatire]

if that doesn't help be sure to include your SQL and error so we can better help. You didn't give us much to go on.

Human beings, who are almost unique in having the ability to learn from the experience of others, are also remarkable for their apparent disinclination to do so.
-Douglas Adams (1952-2001)

 

[ivalum21]

Here is my SQL right now:

SELECT CustCity, AVG(CustBal) AS AvgBal, left(CustZip, 5) AS ZipCode
FROM Customer
WHERE CustState = 'WA'
GROUP BY CustCity

It works fine when i take out the ZipCode part in my SELECT statement...how can i get it to work with the ZipCode part?

 

[r937]

you get it to work with the zipcode part -- which is a non-aggregate -- by including it in the SELECT list

SELECT CustCity
     , AVG(CustBal) AS AvgBal
     , left(CustZip, 5) AS ZipCode
  FROM Customer
 WHERE CustState = 'WA'
GROUP 
    BY CustCity
     , left(CustZip, 5)

rudy

http://r937.com/

SQL Consulting

 

[ecobb]

Add it to your GROUP BY clause.



Hope This Helps!

Ecobb

"My work is a game, a very serious game." - M.C. Escher

 

[r937]

doh! that's what i meant (see the code) even though i didn't say it

rudy

http://r937.com/

SQL Consulting

 

[ivalum21]

Okay...that worked, thanks a lot!

But I was trying this for the fun of it, what if i needed to add in that the AvgBal needed to be less than 100? I tried this:

SELECT CustCity, AVG(CustBal) AS AvgBal, left(CustZip, 5) AS ZipCode
FROM Customer
WHERE CustState = 'WA'
AND AvgBal < 100
GROUP BY CustCity, left(CustZip, 5)

But when i go to run it, it gives me a pop up box that wants me to input a value for AvgBal?? I don't want that...

 

[r937]

you want HAVING, not WHERE

SELECT CustCity
     , AVG(CustBal) AS AvgBal
     , left(CustZip, 5) AS ZipCode
  FROM Customer
 WHERE CustState = 'WA'
GROUP 
    BY CustCity
     , left(CustZip, 5) 
having AVG(CustBal) < 100

rudy

http://r937.com/

SQL Consulting

 

[ivalum21]

Thank you, that works perfect.