Re: Insert...on duplicate with aggregate
Awesome! Thanks Baron, works perfectly.. Phil On Tue, Feb 26, 2008 at 10:06 PM, Baron Schwartz [EMAIL PROTECTED] wrote: Hi! On Tue, Feb 26, 2008 at 7:04 PM, Phil [EMAIL PROTECTED] wrote: I have a table countrystats defined as CREATE TABLE IF NOT EXISTS `countrystats` ( `proj` char(6) NOT NULL default '', `country` char(50) NOT NULL default '', `score` double default NULL, `nusers` int(11) default NULL, `RAC` double default NULL, `last_update` double default NULL, PRIMARY KEY (`proj`,`country`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; To get the data I can do the following select 'rsi',country,sum(metric1) as total,count(*),sum(metric2) from user_table group by country; This works fine, but then I tried to populate the table with INSERT INTO countrystats (select 'rsi', country,sum(metric1) as total,count(*) as count,sum(metric2) as sumrac,0 from user_table group by country) on duplicate key update last_update=total - score,score=total,nusers=count,RAC=sumrac; which gives me ERROR 1054 (42S22): Unknown column 'total' in 'field list' now the insert on it's own without the on duplicate works just fine.. so why does the update not like the 'named' column?? Any ideas? Can it be done in a single statement? That is an odd error. But you can work around it this way: INSERT ... SELECT * FROM ( SELECT... GROUP BY ) AS derived_table ON DUPLICATE KEY
joining and grouping
hello i have a table vote which has the columns voter and candidate. i would like to make a list of the most popular candidates *except* those who are on my favorite list. using a sub-select, it's easy: my favorites: select candidate from vote where voter = '$me'; most popular: select candidate from vote group by candidate order by count(*) desc; sub-select: select candidate from vote where candidate not in (select candidate from vote where voter = '$me') group by candidate order by count(*) desc; however, sub-selects are very slow, so i need to find a speedy way. i'm familiar with joins, but don't know how to use it for this case where grouping is involved. please get in touch if you know how to solve it. thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: recursion
At 7:27 AM + 2/27/08, Thufir wrote: I'm reading SQL for dummies and one of the more interesting sections was on recursion. The example query was something like: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT UNION SELECT in.Source, out.Destination FROM ReachableFrom in, FLIGHT out WHERE in.Destination = out.Source ) SELECT * FROM ReachableFrom WHERE Source = Portland; I'm a bit thrown by the union. Can this be simplified to: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT ) SELECT * FROM ReachableFrom WHERE Source = Portland; MySQL does not have WITH RECURSIVE. -- 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]
RE: recursion
I'm not aware of MySQL supporting this feature. Microsoft does and calls it common table expression (CTE). The UNION is necessary as this the part that links the anchor query, Part1 of the UNION to the recursive query, Part2. Part2 of the UNION must reference the produced temporary table called ReachableFrom in your example. The Part2 query is put in an internal loop constantly inserting new rows into the CTE and then referencing them in the next loop. Once the loop no longer generates new rows the CTE stops and the final query is executed. Ed -Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Thufir Sent: Wednesday, February 27, 2008 12:28 AM To: mysql@lists.mysql.com Subject: recursion I'm reading SQL for dummies and one of the more interesting sections was on recursion. The example query was something like: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT UNION SELECT in.Source, out.Destination FROM ReachableFrom in, FLIGHT out WHERE in.Destination = out.Source ) SELECT * FROM ReachableFrom WHERE Source = Portland; I'm a bit thrown by the union. Can this be simplified to: WITH RECURSIVE ReachableFrom (Source, Destination) AS (SELECT Source, Destination FROM FLIGHT ) SELECT * FROM ReachableFrom WHERE Source = Portland; thanks, Thufir -- 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: joining and grouping
I'm confused as to why you need the subselect at all? As it's all the same table why can't you just use select candidate,count(*) as total from vote where voter '$me' group by candidate order by total desc; On Wed, Feb 27, 2008 at 9:04 AM, Olav Mørkrid [EMAIL PROTECTED] wrote: hello i have a table vote which has the columns voter and candidate. i would like to make a list of the most popular candidates *except* those who are on my favorite list. using a sub-select, it's easy: my favorites: select candidate from vote where voter = '$me'; most popular: select candidate from vote group by candidate order by count(*) desc; sub-select: select candidate from vote where candidate not in (select candidate from vote where voter = '$me') group by candidate order by count(*) desc; however, sub-selects are very slow, so i need to find a speedy way. i'm familiar with joins, but don't know how to use it for this case where grouping is involved. please get in touch if you know how to solve it. thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: joining and grouping
Ok then, so select candidate,count(*) as total from vote where (voter '$me' and vote =1) group by candidate order by total desc; On Wed, Feb 27, 2008 at 9:37 AM, Olav Mørkrid [EMAIL PROTECTED] wrote: hi phil, i forgot to mention one thing. the table also has a column called vote which is either 0 (no vote given) or 1 (vote given). this column is required for other purposes. my favorites: select candidate from vote where voter = '$me' and vote = 1; most popular: select candidate from vote where vote = 1 group by candidate order by count(*) desc; when generating the desired list (most popular minus my favorites) it is important that a candidate is excluded from the result set if *i* voted for him -- even if a million other people voted for him. is this clearer? On 27/02/2008, Phil [EMAIL PROTECTED] wrote: I'm confused as to why you need the subselect at all? As it's all the same table why can't you just use select candidate,count(*) as total from vote where voter '$me' group by candidate order by total desc; On Wed, Feb 27, 2008 at 9:04 AM, Olav Mørkrid [EMAIL PROTECTED] wrote: hello i have a table vote which has the columns voter and candidate. i would like to make a list of the most popular candidates *except* those who are on my favorite list. using a sub-select, it's easy: my favorites: select candidate from vote where voter = '$me'; most popular: select candidate from vote group by candidate order by count(*) desc; sub-select: select candidate from vote where candidate not in (select candidate from vote where voter = '$me') group by candidate order by count(*) desc; however, sub-selects are very slow, so i need to find a speedy way. i'm familiar with joins, but don't know how to use it for this case where grouping is involved. please get in touch if you know how to solve it. thanks! -- 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: joining and grouping
hi phil, i forgot to mention one thing. the table also has a column called vote which is either 0 (no vote given) or 1 (vote given). this column is required for other purposes. my favorites: select candidate from vote where voter = '$me' and vote = 1; most popular: select candidate from vote where vote = 1 group by candidate order by count(*) desc; when generating the desired list (most popular minus my favorites) it is important that a candidate is excluded from the result set if *i* voted for him -- even if a million other people voted for him. is this clearer? On 27/02/2008, Phil [EMAIL PROTECTED] wrote: I'm confused as to why you need the subselect at all? As it's all the same table why can't you just use select candidate,count(*) as total from vote where voter '$me' group by candidate order by total desc; On Wed, Feb 27, 2008 at 9:04 AM, Olav Mørkrid [EMAIL PROTECTED] wrote: hello i have a table vote which has the columns voter and candidate. i would like to make a list of the most popular candidates *except* those who are on my favorite list. using a sub-select, it's easy: my favorites: select candidate from vote where voter = '$me'; most popular: select candidate from vote group by candidate order by count(*) desc; sub-select: select candidate from vote where candidate not in (select candidate from vote where voter = '$me') group by candidate order by count(*) desc; however, sub-selects are very slow, so i need to find a speedy way. i'm familiar with joins, but don't know how to use it for this case where grouping is involved. please get in touch if you know how to solve it. thanks! -- 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]
weird select - version 2
Hi, Dont know if my last post got to the list, but since then my problem got a bit more complicated. I know how to solve it using two or more queries, but was wondering if it would be possible using only one: tableA id_tA numof descr 1 0809 john 2 0808 peter table_out id_tOut id_tA ref 12 samantha table_In id_tIn numof ref 10809 something And the result i need is: Table_result id_tAnumof ref 10809 something 20808 samantha In a nutshell, i need a query intelligent enough to make a query to table_Out, see if theres a match for 'id_tA', if there is one, retrieve field ref, otherwise go look in table_In and retrieve ref from there. Is it too complicated (impossible?) to use only one query? Should i just do it the old style, two queries and a php condition between them? Thanks, guys. Pag -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Odd Update Question.
Hi, I'm wondering if the following can be done UPDATE tbe_gallery SET tbe_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper WHERE tbe_gallery.gallery_id = tbe_images.img_rel_id AND tbe_images.img_orig_filename = tbe_gsa.gsa_id Let me explain: I have 3 tables and only 1 of them has the correct data which I need to update the other two. The SQL statement above is based upon the following select statement below: SELECT tbe_gsa.gsa_id, tbe_gallery.gallery_id, tbe_gallery.gallery_title, tbe_gallery.gallery_price, tbe_gsa.gsa_sin, tbe_gsa.gsa_paperprice, tbe_gsa.gsa_canvasprice FROM tbe_gsa INNER JOIN tbe_images ON tbe_gsa.gsa_id = tbe_images.img_orig_filename INNER JOIN tbe_gallery ON tbe_images.img_rel_id = tbe_gallery.gallery_id ORDER BY gsa_id ASC This statement works just fine. However the table tbe_gsa contains the necessary column tbe_gsa.gsa_paperprice which has a match field of gsa_id, which matches a field in the images table called tbe_images.img_orig_filename, and the images table contains a match field called tbe_images.img_rel_id, which matches a field in the gallery table called tbe_galery.gallery_id. So my question is how do use the corresponding match fields to update the necessary fields so that... be_gsa.gsa_paperprice = tbe_gallery.gallery_gsaprice_paper Is my statement above anywhere close to what it should be Miles. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
