2 Replies - 1922 Views - Last Post: 01 April 2010 - 07:45 PM

#1 Student2010  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 30-March 10

AVG-TOP Function in ACCESS

Posted 01 April 2010 - 11:43 AM

Hello

I am working on a query that requires me to find the models of laptops whose prices are lower than the average price of laptop models. Output a table consisting of two columns: (model, price). Sort the result by prices in ascending order.

I was wondering IF I should use the TOP function or the AVG/MIN? While using the TOP it created a query with (1 Model, 1 Price). I also tried the MIn/AvG BUT I received an error.

Please see the input listed

SELECT TOP 1 Model, Count(*) AS Price
FROM Laptop
GROUP BY Model;
GROUP BY Price ASC;

Or should I use

SELECT Model, Price
FROM Laptop
Where Lower Price <= AVG Price
GROUP BY Price ASC;

Is This A Good Question/Topic? 0
  • +

Replies To: AVG-TOP Function in ACCESS

#2 keakTheGEEK  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: 107
  • View blog
  • Posts: 344
  • Joined: 23-February 10

Re: AVG-TOP Function in ACCESS

Posted 01 April 2010 - 12:17 PM

Hi Student2010,

If you want ALL the laptop models that have a price <= the average of all the laptop prices, then you definitely aren't going to want to use Top 1. The top function only selects the number of records that you specify, i.e. Top 10, would only give you 10 records back. I believe that your second query example is the better approach, as that is querying all the records that meet your criteria. It looks to me that you're probably receiving the error because of how you are using the AVG f(n). Keep in mind that sql is a query language so (with a few exceptions) you are going to have a select statement to get your results. Again, with your second query example you are on the right track. Try using the AVG function like this though:
SELECT Model, Price
FROM Laptop
Where Price <= (SELECT AVG(Price) FROM Laptop)
ORDER BY Price ASC



See if that works. Also, you probably received an error from the word Lower. I don't think that is a field in your table, is it?

It looks like you are doing a great job in figuring this stuff out though. SQL is definitely a different animal than most other programming languages. Keep in mind the SELECT statement. It's used almost every time when returning sets of records. GOOD LUCK!

EDIT: Realized that the GROUP BY isn't correct either. That will cause an error because you don't have Model in your GROUP BY, but that won't sort it for you though, use ORDER BY Price Asc to get the results ordered by price

Also: Not sure if you got my response from your question you posted on my profile, but yes, use the forums to post your questions as that will get your responses a lot sooner...

This post has been edited by keakTheGEEK: 01 April 2010 - 02:25 PM

Was This Post Helpful? 1
  • +
  • -

#3 Student2010  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 9
  • Joined: 30-March 10

Re: AVG-TOP Function in ACCESS

Posted 01 April 2010 - 07:45 PM

KeakTheGEEK

I did noticed an error with the Group BY and immediately changed to Order BY.. I think I am getting there.

Oh yes, I did read your comment regarding posting to the forum.

Thanks AGAIN
Student2010

This post has been edited by Student2010: 01 April 2010 - 07:50 PM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1