You are not allowed to use grouping functions in the WHERE clause. To restrict on grouped values, you have to put them into the HAVING clause.
Cheers /rudy -----Original Message----- From: Roy Walker [mailto:[EMAIL PROTECTED] Sent: dinsdag 15 juli 2003 23:25 To: Rudy Metzger; [EMAIL PROTECTED] Subject: RE: Complex select statement Still having a problem with this. Still have one last thing that isn't working. This is MySQL 4.0.13. UPDATE table_tmp,table2_daily SET table_tmp.period_count = table_tmp.count - table2.count WHERE table_tmp.id = table2.id AND MAX(table2.timestamp); This is giving me: ERROR 1111: Invalid use of group function. I am trying to update the period_count field for all the records in table_tmp, by setting the equal to the count from table_tmp MINUS the count from table2 where the id's match and it is the newest record in table2. Any ideas? Please don't tell me to how to do it 4.1! Please! :) Roy -----Original Message----- From: Rudy Metzger [mailto:[EMAIL PROTECTED] Sent: Monday, July 14, 2003 3:49 AM To: Roy Walker; [EMAIL PROTECTED] Subject: RE: Complex select statement I never heard before that you can use a select statement in an arithmetic expression. Only ALL, ANY, MIN, MAX, =, (and some other which do not come to my mind quickly) should work. Anyway, it is considered a "subselect" and therefore does not work yet. However, in 4.1 you should also be able to formulate it like this: INSERT INTO table2( id, count, period_count, date ) SELECT t1.id, t1.count, t1.count - x.count, to_days(now()-1) FROM table1 t1, (SELECT count FROM table2 WHERE id = t1.id ORDER BY timestamp desc LIMIT 1) x This would also be the way how I would do it in a different DB (e.g. ORACLE). Cheers /rudy -----Original Message----- From: Roy Walker [mailto:[EMAIL PROTECTED] Sent: vrijdag 11 juli 2003 18:30 To: [EMAIL PROTECTED] Subject: RE: Complex select statement Eva, Thanx for your help. Still having an issue with this. I forgot to mention I am running MySQL 4.0.13. Tried both of these statements: INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count, (count - (SELECT count FROM table2 WHERE id=id1 order by timestamp desc limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1; INSERT INTO table2 (id,count,period_count,date) SELECT @id1:=id, count, (count - (SELECT count FROM table2 WHERE [EMAIL PROTECTED] order by timestamp desc limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1; They both give me an error for the 'SELECT count FROM table2 WHERE id=id1' section. Is this illegal syntax? Would this be considered a subselect? Thanx, Roy -----Original Message----- From: "Paracková Eva, Ing" [mailto:[EMAIL PROTECTED] Sent: Friday, July 11, 2003 12:30 AM To: Roy Walker Subject: RE: Complex select statement try this: INSERT INTO table2 (id,count,period_count,date) SELECT id as id1, count, (count - (SELECT count FROM table2 WHERE id=id1 order by timestamp desc limit 1)) AS period_count, (TO_DAYS(NOW() -1)) from table 1; i am not sure, the subselect is ok. if the "id1" alias will not work, then try to use a variable (... SELECT @id1:=id ... WHERE [EMAIL PROTECTED] ...). i just corrected a few syntax mistakes in your query. eva -----Original Message----- From: Roy Walker [mailto:[EMAIL PROTECTED] Sent: Friday, July 11, 2003 7:19 AM To: [EMAIL PROTECTED] Subject: Complex select statement If anyone could tell me what I am doing wrong here, I would greatly appreaciate it. Have the following tables: table1: id, count table2: id, count, period_count, date, timestamp Trying to do the following; get all rows from table 1 and insert them into table2 while setting period_count to count.table1 minus the most recent entry for that id in count.table2, then set the date field to the previous day. Here goes: INSERT INTO table1 (id,count,period_count,date) SELECT id,count FROM table1, (count.table1 - (SELECT count FROM table2 WHERE MAX(timestamp) AND id.table2=id)) AS period_count, date='TO_DAYS(NOW() -1)'; Sorry if this is easy, but I could not find an example of any mathematic functions while doing an INSERT ... SELECT. Thanx, Roy -- 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]
