Re: AVG Function
Hi Craig, Sorry this is such a slow response but I have been swamped and I didn't see where anyone else has responded yet. First, we need to calculate the average ID per user but round the average to the nearest whole number. CREATE TEMPORARY TABLE tmpResults SELECT cast((AVG(id)+ .5) as integer) as average,u.user_id, u.username FROM users u INNER JOIN routes rt ON u.user_id = rt.user_id INNER JOIN ranking rnk ON rnk.rating = rt.rating WHERE username='$username' GROUP BY u.user_ID, username Now, maybe we can give you the results you wanted. SELECT rnk.rating as user_avg, tr.username, tr.user_id FROM tmpResults tr INNER JOIN ranking rnk ON tr.average = rnk.id OR if you wanted to see all of the Routes with the average information and user information all in the same query SELECT rnk.rating as user_avg, tr.username, tr.user_id, r.rating, r.route FROM tmpResults tr INNER JOIN ranking rnk ON tr.average = rnk.id INNER JOIN routes r ON r.user_id = tr.user_id As always, we need to clean up after ourselves: DROP TABLE tmpResults Hope this helped... Shawn Green Database Administrator Unimin Corporation - Spruce Pine Craig Hoffman [EMAIL PROTECTED] wrote on 08/24/2004 09:24:55 PM: Hey Everyone, I can you some assistance on this query. I have three tables one is called ranking and the other is called routes and finally the users table. The users table is not really important. The ranking table looks like this: id rating 15.0 2 5.1 3 5.2 4 5.3 5 5.3a 6 5.3b and so on... The routes table looks like this: user_id route rating 1 somename 5.2 1 5.3 1 5.3a Here's my query: SELECT ranking.rating, AVG(id), users.username, users.user_id, routes.rating, routes.user_id FROM ranking, routes, users WHERE username='$username' AND users.user_id = routes.user_id AND ranking.rating = routes.rating GROUP BY username What I am trying to do is find the average rating for this user. For example: 5.2 = 3 5.3 = 4 5.3a = 5 ___ 3 + 4 + 5 = 12 / 3 = 4 So 4 = 5.3 The average for this user would be 5.3. Any help would be most appreciated. Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AVG Function
Craig Hoffman wrote:
Mark,
Yes its close and thank you. The problem I am having is I am able to
generate the correct ranking.id for that particular user but I can't
seem to make it equal the ranking.rating.
ID ranking.rating
9 = 5.6 (example)
Here's my query:
SELECT routes.user_id, ranking.rating, ROUND(AVG(ranking.id), 0)
avg_ranking, users.username, users.user_id, routes.rating FROM ranking,
routes, users WHERE username='$username' AND routes.user_id =
users.user_id AND ranking.rating = routes.rating GROUP BY routes.user_id
//echo some stuff out
echo(td align='right'.$row[ranking.rating]. /td);
I know I need to make the avg_ranking or the ranking.id = ranking.rating
but I can't seem to get it work. Any more suggestions? Again thanks
for all your help.
-- Craig
rant9 is never equal to 5.6./rant
Sorry, but I teach math and that sort of thing drives me nuts.
I mention this because I think it is part of why this is causing you
trouble. For every row of ranking you've shown us, ranking.id does not
equal ranking.rating, so you cannot make it equal the ranking.rating.
Yes, I know what you meant. You're using that as shorthand for getting the
corresponding ranking.rating for the ranking.id. But that shorthand
obscures the problem. You need to select the rows for the given user to
look at them to calculate the average. You need the resulting average id to
look up the corresponding rating. You see? By avoiding the shorthand, I
think it is a little more obvious that this takes 2 steps. You cannot
magically select the row with the average id at the same time you are
selecting the rows to be averaged.
If you want the average for a particular user, say user_id = 1, as in your
example, you can do it in 2 steps with a user variable (see sample data at
the end):
SELECT @avg_rank_id:=ROUND(AVG(ranking.id), 0) avg_rank_id
FROM ranking, routes
WHERE routes.user_id = 1
AND ranking.rating = routes.rating;
+-+
| avg_rank_id |
+-+
| 4 |
+-+
1 row in set (0.00 sec)
SELECT * FROM ranking WHERE id = @avg_rank_id;
+++
| id | rating |
+++
| 4 | 5.3|
+++
1 row in set (0.00 sec)
If you want to get the average for each user_id, you can do it in 2 steps
with a temporary table:
CREATE TEMPORARY TABLE rank_avg
SELECT user_id, ROUND(AVG(ranking.id), 0) AS avg_rank_id
FROM ranking, routes
WHERE ranking.rating = routes.rating
GROUP BY routes.user_id;
SELECT rank_avg.user_id, ranking.rating
FROM rank_avg, ranking
WHERE ranking.id = rank_avg.avg_rank_id;
+-++
| user_id | rating |
+-++
| 1 | 5.3|
| 2 | 5.3a |
+-++
2 rows in set (0.00 sec)
DROP TABLE rank_avg;
{I see that Shawn has sent you a solution equivalent to this as I was typing.}
If you have mysql 4.1, you can use a (correlated) subquery to combine both
steps in one query:
SELECT rt.user_id, rnk.rating
FROM routes rt, ranking rnk
WHERE rnk.id = (SELECT ROUND(AVG(ranking.id), 0)
FROM ranking, routes
WHERE ranking.rating = routes.rating
AND routes.user_id = rt.user_id)
GROUP BY rt.user_id;
+-++
| user_id | rating |
+-++
| 1 | 5.3|
| 2 | 5.3a |
+-++
2 rows in set (0.01 sec)
This will be inefficient, however, so I don't recommend it.
Finally, as a mathematician, I must point out that calling this the average
ranking is almost certainly misleading. Aside from the rounding, you are
treating your ratings as if they were on a linear scale, but their values
imply otherwise. That is, what you are doing assumes that the difference
between 5.3a and 5.3b is the same as the difference between 5.1 and 5.2. I
don't know anything about your data other than what you've told us, but I'd
be surprised if that assumption were accurate.
Michael
Data for the above examples:
USE test;
DROP TABLE IF EXISTS ranking;
CREATE TABLE ranking
(
id INT(3) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
rating VARCHAR(5)
);
INSERT INTO ranking
VALUES (1, '5.0'), (2, '5.1'), (3, '5.2'),
(4, '5.3'), (5, '5.3a'), (6, '5.3b'),
(7, '5.4'), (8, '5.5'), (9, '5.6');
DROP TABLE IF EXISTS routes;
CREATE TABLE routes
(
user_id INT(3) UNSIGNED,
route CHAR(7),
rating CHAR(4)
);
INSERT INTO routes
VALUES (1, 'Route 1','5.2'), (1, 'Route 2', '5.3'), (1, 'Route 3', '5.3a'),
(2, 'Route 1','5.2'), (2, 'Route 2', '5.3'), (2, 'Route 3', '5.6');
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AVG Function
Mark,
Yes its close and thank you. The problem I am having is I am able to
generate the correct ranking.id for that particular user but I can't
seem to make it equal the ranking.rating.
ID ranking.rating
9 = 5.6 (example)
Here's my query:
SELECT routes.user_id, ranking.rating, ROUND(AVG(ranking.id), 0)
avg_ranking, users.username, users.user_id, routes.rating FROM ranking,
routes, users WHERE username='$username' AND routes.user_id =
users.user_id AND ranking.rating = routes.rating GROUP BY
routes.user_id
//echo some stuff out
echo(td align='right'.$row[ranking.rating]. /td);
I know I need to make the avg_ranking or the ranking.id =
ranking.rating but I can't seem to get it work. Any more suggestions?
Again thanks for all your help.
-- Craig
On Aug 25, 2004, at 12:48 AM, Mark C. Stafford wrote:
Hi Craig,
It sounds to me as though you're practically there. My syntax is a
little different when I do groupings. Here's what I got from your
question. Is it what you wanted...or close enough to get you where
you're going?
Good luck,
Mark
drop table if exists test.ranking;
create table test.ranking (
id int(3) unsigned auto_increment primary key
, rating varchar(5)
);
insert into test.ranking(rating) values('5.0');
insert into test.ranking(rating) values('5.1');
insert into test.ranking(rating) values('5.2');
insert into test.ranking(rating) values('5.3');
insert into test.ranking(rating) values('5.3a');
insert into test.ranking(rating) values('5.3b');
drop table if exists test.routes;
create table test.routes (
user_id int(3) unsigned
, rating varchar(5)
);
insert into test.routes(user_id, rating) values(1, '5.2');
insert into test.routes(user_id, rating) values(1, '5.3');
insert into test.routes(user_id, rating) values(1, '5.3a');
SELECT routes.user_id
, @avg:=ROUND(AVG(ranking.id), 0) avg_ranking
FROM test.ranking
, test.routes
WHERE routes.user_id = 1
AND ranking.rating = routes.rating
GROUP BY routes.user_id
;
SELECT *
FROM test.ranking
WHERE id = @avg
;
+++
| id | rating |
+++
| 4 | 5.3|
+++
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AVG Function
Hey Everyone, I can you some assistance on this query. I have three tables one is called ranking and the other is called routes and finally the users table. The users table is not really important. The ranking table looks like this: id rating 1 5.0 2 5.1 3 5.2 4 5.3 5 5.3a 6 5.3b and so on... The routes table looks like this: user_id route rating 1 somename5.2 1 5.3 1 5.3a Here's my query: SELECT ranking.rating, AVG(id), users.username, users.user_id, routes.rating, routes.user_id FROM ranking, routes, users WHERE username='$username' AND users.user_id = routes.user_id AND ranking.rating = routes.rating GROUP BY username What I am trying to do is find the average rating for this user. For example: 5.2 = 3 5.3 = 4 5.3a = 5 ___ 3 + 4 + 5 = 12 / 3 = 4 So 4 = 5.3 The average for this user would be 5.3. Any help would be most appreciated. Craig -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AVG function in order by clause
Hello, Thanks a lot for the replies. I have changed the query as follows: ** SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID GROUP BY ES.DB_ESTABLISHMENT_ID HAVING AVG(DB_GRADE) 2 ORDER BY AVG(GRADE) ** Now I am having a problem with the order by clause. How can I have the AVG function in the ORDER BY clause or how can I sort by average grade? Thanks in advance, Julien Martin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: AVG function in order by clause
What kind of problem are you having? You can use the ordinal postion. ORDER BY 2 [ASC|DESC]. 2 represents the second column. -Original Message- From: Julien Martin To: '[EMAIL PROTECTED]' Sent: 5/5/04 12:41 PM Subject: AVG function in order by clause Hello, Thanks a lot for the replies. I have changed the query as follows: ** SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID GROUP BY ES.DB_ESTABLISHMENT_ID HAVING AVG(DB_GRADE) 2 ORDER BY AVG(GRADE) ** Now I am having a problem with the order by clause. How can I have the AVG function in the ORDER BY clause or how can I sort by average grade? Thanks in advance, Julien Martin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AVG function in order by clause
Thank you all for your replies. Everything works now!! Julien. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AVG function in order by clause
On Wed, 5 May 2004 19:41:32 +0200 Julien Martin [EMAIL PROTECTED] wrote: Hello, Thanks a lot for the replies. I have changed the query as follows: ** SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID GROUP BY ES.DB_ESTABLISHMENT_ID HAVING AVG(DB_GRADE) 2 ORDER BY AVG(GRADE) ** Now I am having a problem with the order by clause. How can I have the AVG function in the ORDER BY clause or how can I sort by average grade? I THINK you need to alias your column to do this: SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) AS AVG_GRADE FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID GROUP BY ES.DB_ESTABLISHMENT_ID HAVING AVG(DB_GRADE) 2 ORDER BY AVG_GRADE ^ Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AVG function in order by clause
In Oracle you can reference the second field AVG() by the field #. ORDER BY 2 Thanks a lot for the replies. I have changed the query as follows: ** SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID GROUP BY ES.DB_ESTABLISHMENT_ID HAVING AVG(DB_GRADE) 2 ORDER BY AVG(GRADE) ** Now I am having a problem with the order by clause. How can I have the AVG function in the ORDER BY clause or how can I sort by average grade? Thanks in advance, Julien Martin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AVG function in order by clause
Pretty simple, declare an alias in your query and use it in the ORDER BY, i.e.: SELECT DB_ESTABLISHMENT_NAME,AVG(DB_GRADE) AS AVGGRADEFROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ONES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_IDGROUP BY ES.DB_ESTABLISHMENT_IDHAVING AVG(DB_GRADE) 2ORDER BYAVGGRADE Hope that helps! Message from Julien Martin [EMAIL PROTECTED] at 2004-05-05 19:41:32 -- Hello,Thanks a lot for the replies. I have changed the query as follows:**SELECT DB_ESTABLISHMENT_NAME,AVG(DB_GRADE)FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ONES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_IDGROUP BY ES.DB_ESTABLISHMENT_IDHAVING AVG(DB_GRADE) 2ORDER BYAVG(GRADE)**Now I am having a problem with the order by clause. How can I have the AVGfunction in the ORDER BY clause or how can I sort by average grade?Thanks in advance,Julien Martin.-- MySQL General Mailing ListFor list archives: http://lists.mysql.com/mysqlTo unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Best regards Carsten R. Dreesbach mailto:[EMAIL PROTECTED] Senior Consultant Systar Inc. 8000 Westpark Dr Suite 450 McLean VA 22102 USA Tel: 703 5568436 Fax: 703 5568430 Cel: 571 2137904 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: AVG function in order by clause
At 19:41 +0200 5/5/04, Julien Martin wrote: Hello, Thanks a lot for the replies. I have changed the query as follows: ** SELECT DB_ESTABLISHMENT_NAME, AVG(DB_GRADE) FROM ESTABLISHMENTS ES LEFT OUTER JOIN GRADES GR ON ES.DB_ESTABLISHMENT_ID=GR.DB_ESTABLISHMENT_ID GROUP BY ES.DB_ESTABLISHMENT_ID HAVING AVG(DB_GRADE) 2 ORDER BY AVG(GRADE) ** Now I am having a problem with the order by clause. How can I have the AVG function in the ORDER BY clause or how can I sort by average grade? As others have noted, you can alias the column and refer to the alias in the ORDER BY clause, or refer to it by position in the SELECT list. But I suspect the problem with the query above is that you refer to the column as DB_GRADE in the SELECT list and HAVING clause, but as GRADE in the ORDER BY clause. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
