Mysql 5.1 union with group by for results
Good day all I am hoping someone can assist me in the following. One of our servers were running mysql 5.0 still and as part of a phased upgrade route we have upgraded to version 5.1. However since the upgrade, the query below gives us an error stating that the syntax is incorrect and I simply cant seem to find out what is actually wrong as all tests and changes have been giving us the same. I have tried many suggestions from the net but to no avail. The query is as follows: (SELECT SUBSTRING(t.Day,1,7) AS Date, a.Name AS Account, a.Status AS AccountStatus, c.Name AS Login, t.Service AS Service, n.name AS Network, tc.Name AS Toc, t.SrcAddress AS FromAddress, SUM(t.Count) AS COUNT FROM statstx_archive t LEFT JOIN account a ON t.AccountID=a.ID LEFT JOIN client2 c ON t.ClientID=c.ID LEFT JOIN tocname tc ON t.TOC=tc.toc LEFT JOIN network n ON t.NetworkID=n.ID WHERE t.toc=1 AND SUBSTRING(t.Day,1,7) = '2013-08' AND SUBSTRING(t.Day,1,7) = '2013-11') UNION (SELECT SUBSTRING(t.Day,1,7) AS Date, a.Name AS Account, a.Status AS AccountStatus, c.Name AS Login, t.Service AS Service, n.name AS Network, tc.Name AS Toc, t.SrcAddress AS FromAddress, SUM(t.COUNT) AS COUNT FROM statstx t LEFT JOIN account a ON t.AccountID=a.ID LEFT JOIN client2 c ON t.ClientID=c.ID LEFT JOIN tocname tc ON t.TOC=tc.toc LEFT JOIN network n ON t.NetworkID=n.ID WHERE t.toc=1 AND SUBSTRING(t.Day,1,7) = '2013-08' AND SUBSTRING(t.Day,1,7) = '2013-11') GROUP BY t.AccountID, t.ClientID, t.Service, t.NetworkID, t.Toc, t.SrcAddress, SUBSTRING(t.Day,1,7) When running the queries individually it works fine, and when removing the group by it also runs, however the moment we put the group by section back then it falls over. Any help would by appreciated. Regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql 5.1 union with group by for results
Hi Machiel, On 8/11/2013 20:04, Machiel Richards wrote: Good day all I am hoping someone can assist me in the following. One of our servers were running mysql 5.0 still and as part of a phased upgrade route we have upgraded to version 5.1. However since the upgrade, the query below gives us an error stating that the syntax is incorrect and I simply cant seem to find out what is actually wrong as all tests and changes have been giving us the same. I have tried many suggestions from the net but to no avail. The query is as follows: Using a shorter but equivalent query, you have: (SELECT t.id, t.name, SUM(val) FROM t1 t) UNION (SELECT t.id, t.name, SUM(val) FROM t2 t) GROUP BY t.id, t.name; That does not work in 5.0 either (at least in 5.0.96): ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY t.id, t.name' at line 4 The issue is that you are trying grouping the entire UNION result, but at that point there is no SELECT any longer - there is just the result set. You are also referencing tables that exists inside each of the SELECT statements, but at the time the GROUP BY is reached, there are no tables. Note that as written the two SELECT parts will also give non-deterministic results as you have an aggregate function but no GROUP BY, so the values of id and val1 will be random. What you probably want instead is either: (SELECT t.id, t.name, SUM(val) FROM t1 t GROUP BY t.id, t.name) UNION (SELECT t.id, t.name, SUM(val) FROM t2 t GROUP BY t.id, t.name); or SELECT a.id, a.name, SUM(val) FROM ( (SELECT t.id, t.name, t.val FROM t1 t) UNION (SELECT t.id, t.name, t.val FROM t2 t) ) a GROUP BY a.id, a.name; On a side note: AND SUBSTRING(t.Day,1,7) = '2013-08' AND SUBSTRING(t.Day,1,7) = '2013-11') Assuming t.Day is a date, datetime, or timestamp column, you can rewrite that WHERE clause to something like (depending on the exact data type): t.Day BETWEEN '2013-08-01 00:00:00' AND '2013-11-30 23:59:59' or t.Day = '2013-08-01 00:00:00' AND t.Day '2013-12-01 00:00:00' That way you will be able to use an index for that condition. Best regards, Jesper Krogh MySQL Support
Re: Mysql 5.1 union with group by for results
- Original Message - From: Machiel Richards machiel.richa...@gmail.com ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY t.AccountID, I suspect your query has never been doing what you think at all, and you need to select [fields] from ( select fields1 blahblah UNION select fields2 blahblah) unionized group by blurb that is, wrap the entire union in a virtual table and do the group by on that. -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql 5.1 union with group by for results
Happiness, that gave me what I was looking for. Thank you Johan. I have tested the option you gave me but my brackets was in the wrong place. On 08/11/2013 13:23, Johan De Meersman wrote: - Original Message - From: Machiel Richards machiel.richa...@gmail.com ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY t.AccountID, I suspect your query has never been doing what you think at all, and you need to select [fields] from ( select fields1 blahblah UNION select fields2 blahblah) unionized group by blurb that is, wrap the entire union in a virtual table and do the group by on that. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Mysql 5.1 union with group by for results
Hi Jesper I was just discussing this with the development manager now and the following was noted. - The query was written for mysql 4.0 originally and it seems that in version 5.0 they had enabled some legacy support stuff ( I am not too familiar with this as it is before my mysql time ;-) ). - I have now explained to them what the problem is and they will be working with the developers to rewrite all these queries. Regards Machiel. On 08/11/2013 13:27, Jesper Wisborg Krogh wrote: Hi Machiel, On 8/11/2013 20:04, Machiel Richards wrote: Good day all I am hoping someone can assist me in the following. One of our servers were running mysql 5.0 still and as part of a phased upgrade route we have upgraded to version 5.1. However since the upgrade, the query below gives us an error stating that the syntax is incorrect and I simply cant seem to find out what is actually wrong as all tests and changes have been giving us the same. I have tried many suggestions from the net but to no avail. The query is as follows: Using a shorter but equivalent query, you have: (SELECT t.id, t.name, SUM(val) FROM t1 t) UNION (SELECT t.id, t.name, SUM(val) FROM t2 t) GROUP BY t.id, t.name; That does not work in 5.0 either (at least in 5.0.96): ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GROUP BY t.id, t.name' at line 4 The issue is that you are trying grouping the entire UNION result, but at that point there is no SELECT any longer - there is just the result set. You are also referencing tables that exists inside each of the SELECT statements, but at the time the GROUP BY is reached, there are no tables. Note that as written the two SELECT parts will also give non-deterministic results as you have an aggregate function but no GROUP BY, so the values of id and val1 will be random. What you probably want instead is either: (SELECT t.id, t.name, SUM(val) FROM t1 t GROUP BY t.id, t.name) UNION (SELECT t.id, t.name, SUM(val) FROM t2 t GROUP BY t.id, t.name); or SELECT a.id, a.name, SUM(val) FROM ( (SELECT t.id, t.name, t.val FROM t1 t) UNION (SELECT t.id, t.name, t.val FROM t2 t) ) a GROUP BY a.id, a.name; On a side note: AND SUBSTRING(t.Day,1,7) = '2013-08' AND SUBSTRING(t.Day,1,7) = '2013-11') Assuming t.Day is a date, datetime, or timestamp column, you can rewrite that WHERE clause to something like (depending on the exact data type): t.Day BETWEEN '2013-08-01 00:00:00' AND '2013-11-30 23:59:59' or t.Day = '2013-08-01 00:00:00' AND t.Day '2013-12-01 00:00:00' That way you will be able to use an index for that condition. Best regards, Jesper Krogh MySQL Support -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: getting certain rows from a group by
On Wed, Sep 19, 2012 at 6:56 PM, Rick James rja...@yahoo-inc.com wrote: my counts are 3 times too much. Without studying the code, I would guess that there is a JOIN between he data that needs COUNTing and the GROUP BY for the COUNT. That is, it collects more 'joined' rows before counting. Fixing it will probably make the query even messier. The issue was with the last run join. I had to change the where clause in the join from: lr.target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) to: lr.target_name_id = q1.target_name_id AND lr.ep = q1.ep In fact I had to do this for all the joins. Thanks again for all your help! -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, September 19, 2012 3:22 PM To: Rick James Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: Re: getting certain rows from a group by Well, I'm getting the proper rows from the 4 joins I added, but now I'm seeing a weird side effect - my counts are 3 times to much. The query is really huge and nasty now, but I'm going to paste it below. In the outer most select, Wafers, Rerun, Runs, and Count are 3 times what they should be. If I take off the joins, I get the correct counts. I can't see why that's happening. SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, FORMAT(SUM(numonep)/(COUNT(*)+(SUM(GREATEST(q1.reruns, 0, 1) as 'Sites/Wafer', DATE_FORMAT(MAX(LastRun), '%m/%d/%y') as Last Run, SUM(GREATEST(q1.reruns, 0)) as Rerun, COUNT(*)+SUM(GREATEST(q1.reruns, 0)) as Runs, FORMAT(avgbottom, 1) as Avg Bottom, FORMAT(3*stdbottom, 2) as 3 Sig, FORMAT(maxbottom, 1) as Max, FORMAT(minbottom, 1) as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, CONCAT(FORMAT(100*SUM(numbottoms)/SUM(numonep), 1), ' %') as % Success, FORMAT(3*stdbottom/avgbottom, 2) as 3Sig/Avg, FORMAT(AVG(avgbottom), 1) as 'Wafer Avg', FORMAT(AVG(Wafer3Sigma), 2) as 'Wafer 3 Sigma', FORMAT(AVG(Ranges), 1) as 'Avg Range', FORMAT(3*STD(Ranges), 2) as '3Sig of Ranges', FORMAT(MAX(Ranges), 1) as 'Max Range', FORMAT(MIN(Ranges), 1) as 'Min Range', FORMAT((SUM(numonep) - SUM(numbottoms))/COUNT(*), 1) as 'NAs/Wafer', mx.maxImage as maxImage, mn.minImage as minImage, lr.lrImage as lrlrImage, cm.cmImage as cmcmImage FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE TRUE AND data_cst.target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1 JOIN (SELECT data_cstimage.name as minImage, data_cst.bottom, data_cst.target_name_id, data_cst.date_time, data_cst.lot_id, data_cst.tool_id, data_cst.roiname, data_cst.recipe_id, data_cst.ep FROM data_cstimage, data_cst WHERE data_cstimage.id = data_cst.image_measurer_id) mn ON mn.bottom = q1.minbottom AND mn.target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND mn.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' JOIN (SELECT data_cstimage.name as maxImage, data_cst.bottom, data_cst.target_name_id, data_cst.date_time, data_cst.lot_id, data_cst.tool_id
Re: getting certain rows from a group by
2012/09/18 06:53 -0400, Larry Martell This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). There is a standard and ugly way of getting such a thing, but it entails repeating the table reference (supposing you want any, not every, to match): SELECT data_cst.target_name_id, image_measurer_id, nb, xb, vb, xt, bottom FROM (SELECT Min(bottom) AS nb, Max(bottom) AS xb, MIN(ABS(Avg(bottom) - bottom)) AS vb, Max(date_time) AS xt FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND DATE(data_cst.date_time) = '2010-03-04' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) AS st JOIN data_cst ON st.target_name_id = data_cst.target_name_id AND (nb = bottom OR xb = bottom OR vb = bottom OR date_time = xt) One record will be returned for every row that holds a relevant extremum, not guaranteed to be unique. This query pertains only to your original subquery, not the whole query. To get the result to which you refer, join this to your original query, to which you have added something like vb: SELECT * FROM (original query) JOIN (this query) ON nb = minbottom AND xb = maxbottom AND xt = Last Run AND vb = AND (original query).target_name_id = (this_query).target_name_id --but I am not confident in the result. There are problems in the original query, the biggest that of avgbottom, stdbottom, maxbottom, minbottom none are aggregated over wafer_id. Therefore, it is not certain from which record from q1 they are returned. MySQL tends to pick the first that fits, but not only is nothing guaranteed, it is explicitly written that if such not aggregated fields appear, the output is not determinate unless all pertinent are equal, the possibility wherof the reason for allowing it. When that has been handled, it is needful to change the foregoing query to match that one in two levels, because averaging is not associative (there is also the problem of equality-testing on generated floating-point numbers). If it were only MAX and MIN, one level of not GROUPing BY wafer_id would be all right. By the way, in the original query, I suggest instead of data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' this, DATE(data_cst.date_time) = '2010-03-04' Instead of ... q1, data_target WHERE data_target.id = target_name_id this would be better: ... q1 JOIN data_target ON data_target.id = target_name_id I believe that 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) = 100*SUM(numbottoms)/SUM(numonep) and SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) = SUM(GREATEST(reruns, 0)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: getting certain rows from a group by
On Tue, Sep 18, 2012 at 7:41 PM, Rick James rja...@yahoo-inc.com wrote: SELECT ((the appropriate id)) -- -- FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id = data_cst.target_name_id GROUP BY rollup.Target, rollup.EP ORDER BY ABS(data_cst.bottom - rollup.`Avg Bottom`) LIMIT 1 -- -- rollup is the name of the temp table that I was testing with. It contains the output of the original query. I don't have that in the real world. That's my issue - I can't figure out how to join with the result set and apply the order by to the join condition. What would I use in place of rollup to get the 'Avg Bottom'? Here's what my query looks like now: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Runs, avgbottom as Avg Bottom, 3*stdbottom as 3 Sig, maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success, 3*stdbottom/avgbottom as 3Sig/Avg, AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range', (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer', mx.id as maxID, mn.id as minID, lr.id as lrID FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1 join data_cst mn ON mn.bottom = q1.minbottom join data_cst mx on mx.bottom = q1.maxbottom join data_cst lr on lr.date_time = q1.LastRun, data_target WHERE data_target.id = q1.target_name_id GROUP BY q1.target_name_id, q1.ep; -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 2:57 PM To: Rick James Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 4:01 PM, Rick James rja...@yahoo-inc.com wrote: SELECT mn.* mx.* FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max That is, the FROM finds the value for the desired row. The JOIN then gets to the rest of the fields. Caveat: If two rows have bottom = @min, it returns both. Thanks a lot Rick! This was super helpful. I've got it working for 3 of the 4 cases - min(bottom), max(bottom), and max(date_time). But I can't figure out how to work in the last case - where bottom is closest to avg(bottom). In an individual query I can get it with an order by, like this: SELECT rollup.Target, rollup.EP, rollup.`Avg Bottom`, data_cst.id, data_cst.bottom FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id = data_cst.target_name_id GROUP BY rollup.Target, rollup.EP ORDER BY ABS(data_cst.bottom - rollup.`Avg Bottom`); Any way to work that into another join? Thanks! -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 12:54 PM To: Rick James Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 2:05 PM, Rick James rja...@yahoo-inc.com wrote: For single query... Plan A: Repeat the SELECT as once for each (MIN, AVG, etc) as a subquery in WHERE bottom = ( SELECT MIN(bottom) ... ) Plan B: FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom
Re: getting certain rows from a group by
On Wed, Sep 19, 2012 at 12:04 AM, h...@tbbs.net wrote: 2012/09/18 06:53 -0400, Larry Martell This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). There is a standard and ugly way of getting such a thing, but it entails repeating the table reference (supposing you want any, not every, to match): SELECT data_cst.target_name_id, image_measurer_id, nb, xb, vb, xt, bottom FROM (SELECT Min(bottom) AS nb, Max(bottom) AS xb, MIN(ABS(Avg(bottom) - bottom)) AS vb, Max(date_time) AS xt FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND DATE(data_cst.date_time) = '2010-03-04' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) AS st JOIN data_cst ON st.target_name_id = data_cst.target_name_id AND (nb = bottom OR xb = bottom OR vb = bottom OR date_time = xt) One record will be returned for every row that holds a relevant extremum, not guaranteed to be unique. This query pertains only to your original subquery, not the whole query. To get the result to which you refer, join this to your original query, to which you have added something like vb: SELECT * FROM (original query) JOIN (this query) ON nb = minbottom AND xb = maxbottom AND xt = Last Run AND vb = AND (original query).target_name_id = (this_query).target_name_id Thanks. I will look into doing it like this. I got the min, max, and date conditions to work by adding the joins that Rick suggested: join data_cst mn ON mn.bottom = q1.minbottom join data_cst mx ON mx.bottom = q1.maxbottom join data_cst lr ON lr.date_time = q1.LastRun But now you have me wondering - if this just matching on that one field, then it can be returning a row from data_cst that is not part of the original result set? Do I need to add all the same select criteria to each join? --but I am not confident in the result. There are problems in the original query, the biggest that of avgbottom, stdbottom, maxbottom, minbottom none are aggregated over wafer_id. The inner query aggregates over target_name_id, ep, wafer_id, and the the outer query aggregates that result set over target_name_id, ep. The avg and std are what the client wants - average of averages and standards deviations. As are the min and max - the outer query returns the min and max of all the wafers for each target, ep. Therefore, it is not certain from which record from q1 they are returned. MySQL tends to pick the first that fits, but not only is nothing guaranteed, it is explicitly written that if such not aggregated fields appear, the output is not determinate unless all pertinent are equal, the possibility wherof the reason for allowing it. Yes, if case with dups probably had not been thought of by them, and will have to be addressed at some point. When that has been handled, it is needful to change the foregoing query to match that one in two levels, because averaging is not associative (there is also the problem of equality-testing on generated floating-point numbers). If it were only MAX and MIN, one level of not GROUPing BY wafer_id would be all right. By the way, in the original query, I suggest instead of data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' this, DATE(data_cst.date_time) = '2010-03-04' The date_time was just an example. Depending on user input the query could have times in it. Instead of ... q1, data_target WHERE data_target.id = target_name_id this would be better: ... q1 JOIN data_target ON data_target.id = target_name_id Why is one better then the other? I believe that 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) = 100*SUM(numbottoms)/SUM(numonep) and SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) = SUM(GREATEST(reruns, 0)) Yes, thanks - yours is clearer and I've made those changes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: getting certain rows from a group by
On Wed, Sep 19, 2012 at 12:04 AM, h...@tbbs.net wrote: 2012/09/18 06:53 -0400, Larry Martell This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). There is a standard and ugly way of getting such a thing, but it entails repeating the table reference (supposing you want any, not every, to match): SELECT data_cst.target_name_id, image_measurer_id, nb, xb, vb, xt, bottom FROM (SELECT Min(bottom) AS nb, Max(bottom) AS xb, MIN(ABS(Avg(bottom) - bottom)) AS vb, Max(date_time) AS xt FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND DATE(data_cst.date_time) = '2010-03-04' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) AS st JOIN data_cst ON st.target_name_id = data_cst.target_name_id AND (nb = bottom OR xb = bottom OR vb = bottom OR date_time = xt) This: MIN(ABS(Avg(bottom) - bottom)) Is not valid. It gives: ERROR (HY000): Invalid use of group function Which is why I was doing it with an order by. But I can figure out how to work that into a join, One record will be returned for every row that holds a relevant extremum, not guaranteed to be unique. This query pertains only to your original subquery, not the whole query. To get the result to which you refer, join this to your original query, to which you have added something like vb: SELECT * FROM (original query) JOIN (this query) ON nb = minbottom AND xb = maxbottom AND xt = Last Run AND vb = AND (original query).target_name_id = (this_query).target_name_id --but I am not confident in the result. There are problems in the original query, the biggest that of avgbottom, stdbottom, maxbottom, minbottom none are aggregated over wafer_id. Therefore, it is not certain from which record from q1 they are returned. MySQL tends to pick the first that fits, but not only is nothing guaranteed, it is explicitly written that if such not aggregated fields appear, the output is not determinate unless all pertinent are equal, the possibility wherof the reason for allowing it. When that has been handled, it is needful to change the foregoing query to match that one in two levels, because averaging is not associative (there is also the problem of equality-testing on generated floating-point numbers). If it were only MAX and MIN, one level of not GROUPing BY wafer_id would be all right. By the way, in the original query, I suggest instead of data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' this, DATE(data_cst.date_time) = '2010-03-04' Instead of ... q1, data_target WHERE data_target.id = target_name_id this would be better: ... q1 JOIN data_target ON data_target.id = target_name_id I believe that 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) = 100*SUM(numbottoms)/SUM(numonep) and SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) = SUM(GREATEST(reruns, 0)) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: getting certain rows from a group by
On Wed, Sep 19, 2012 at 10:26 AM, Larry Martell larry.mart...@gmail.com wrote: On Wed, Sep 19, 2012 at 12:04 AM, h...@tbbs.net wrote: 2012/09/18 06:53 -0400, Larry Martell This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). There is a standard and ugly way of getting such a thing, but it entails repeating the table reference (supposing you want any, not every, to match): SELECT data_cst.target_name_id, image_measurer_id, nb, xb, vb, xt, bottom FROM (SELECT Min(bottom) AS nb, Max(bottom) AS xb, MIN(ABS(Avg(bottom) - bottom)) AS vb, Max(date_time) AS xt FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND DATE(data_cst.date_time) = '2010-03-04' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) AS st JOIN data_cst ON st.target_name_id = data_cst.target_name_id AND (nb = bottom OR xb = bottom OR vb = bottom OR date_time = xt) One record will be returned for every row that holds a relevant extremum, not guaranteed to be unique. This query pertains only to your original subquery, not the whole query. To get the result to which you refer, join this to your original query, to which you have added something like vb: SELECT * FROM (original query) JOIN (this query) ON nb = minbottom AND xb = maxbottom AND xt = Last Run AND vb = AND (original query).target_name_id = (this_query).target_name_id Thanks. I will look into doing it like this. I got the min, max, and date conditions to work by adding the joins that Rick suggested: join data_cst mn ON mn.bottom = q1.minbottom join data_cst mx ON mx.bottom = q1.maxbottom join data_cst lr ON lr.date_time = q1.LastRun But now you have me wondering - if this just matching on that one field, then it can be returning a row from data_cst that is not part of the original result set? Do I need to add all the same select criteria to each join? I verified that this is the case. I was not getting the correct data back. I had to add: and mn.target_name_id = q1.target_name_id and mn.ep = q1.ep and mn.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' to each join so I got the correct rows back. So now my only issue is still how to work the bottom is closest to avg(bottom) join into this. --but I am not confident in the result. There are problems in the original query, the biggest that of avgbottom, stdbottom, maxbottom, minbottom none are aggregated over wafer_id. The inner query aggregates over target_name_id, ep, wafer_id, and the the outer query aggregates that result set over target_name_id, ep. The avg and std are what the client wants - average of averages and standards deviations. As are the min and max - the outer query returns the min and max of all the wafers for each target, ep. Therefore, it is not certain from which record from q1 they are returned. MySQL tends to pick the first that fits, but not only is nothing guaranteed, it is explicitly written that if such not aggregated fields appear, the output is not determinate unless all pertinent are equal, the possibility wherof the reason for allowing it. Yes, if case with dups probably had not been thought of by them, and will have to be addressed at some point. When that has been handled, it is needful to change the foregoing query to match that one in two levels, because averaging is not associative (there is also the problem of equality-testing on generated floating-point numbers). If it were only MAX and MIN, one level of not GROUPing BY wafer_id would be all right. By the way, in the original query, I suggest instead of data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' this, DATE(data_cst.date_time) = '2010-03-04' The date_time was just an example. Depending on user input the query could have times in it. Instead of ... q1, data_target WHERE data_target.id = target_name_id this would be better: ... q1 JOIN data_target ON data_target.id = target_name_id Why is one better then the other? I believe that 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) = 100*SUM(numbottoms)/SUM(numonep) and SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) = SUM(GREATEST(reruns, 0)) Yes, thanks - yours is clearer and I've made those changes. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: getting certain rows from a group by
OK, I think I have this working. The last join was this: JOIN (select id, target_name_id, ep,date_time from data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep HAVING count(*) 999 ORDER BY ABS(data_cst.bottom - AVG(bottom))) cm on cm.target_name_id = q1.target_name_id and cm.ep = q1.ep and cm.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' I have do to more testing to ensure I'm getting the correct rows back. Thanks for all the help! On Wed, Sep 19, 2012 at 10:07 AM, Larry Martell larry.mart...@gmail.com wrote: On Tue, Sep 18, 2012 at 7:41 PM, Rick James rja...@yahoo-inc.com wrote: SELECT ((the appropriate id)) -- -- FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id = data_cst.target_name_id GROUP BY rollup.Target, rollup.EP ORDER BY ABS(data_cst.bottom - rollup.`Avg Bottom`) LIMIT 1 -- -- rollup is the name of the temp table that I was testing with. It contains the output of the original query. I don't have that in the real world. That's my issue - I can't figure out how to join with the result set and apply the order by to the join condition. What would I use in place of rollup to get the 'Avg Bottom'? Here's what my query looks like now: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Runs, avgbottom as Avg Bottom, 3*stdbottom as 3 Sig, maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success, 3*stdbottom/avgbottom as 3Sig/Avg, AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range', (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer', mx.id as maxID, mn.id as minID, lr.id as lrID FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1 join data_cst mn ON mn.bottom = q1.minbottom join data_cst mx on mx.bottom = q1.maxbottom join data_cst lr on lr.date_time = q1.LastRun, data_target WHERE data_target.id = q1.target_name_id GROUP BY q1.target_name_id, q1.ep; -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 2:57 PM To: Rick James Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 4:01 PM, Rick James rja...@yahoo-inc.com wrote: SELECT mn.* mx.* FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max That is, the FROM finds the value for the desired row. The JOIN then gets to the rest of the fields. Caveat: If two rows have bottom = @min, it returns both. Thanks a lot Rick! This was super helpful. I've got it working for 3 of the 4 cases - min(bottom), max(bottom), and max(date_time). But I can't figure out how to work in the last case - where bottom is closest to avg(bottom). In an individual query I can get it with an order by, like this: SELECT rollup.Target, rollup.EP, rollup.`Avg Bottom`, data_cst.id, data_cst.bottom FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id
Re: getting certain rows from a group by
Well, I'm getting the proper rows from the 4 joins I added, but now I'm seeing a weird side effect - my counts are 3 times to much. The query is really huge and nasty now, but I'm going to paste it below. In the outer most select, Wafers, Rerun, Runs, and Count are 3 times what they should be. If I take off the joins, I get the correct counts. I can't see why that's happening. SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, FORMAT(SUM(numonep)/(COUNT(*)+(SUM(GREATEST(q1.reruns, 0, 1) as 'Sites/Wafer', DATE_FORMAT(MAX(LastRun), '%m/%d/%y') as Last Run, SUM(GREATEST(q1.reruns, 0)) as Rerun, COUNT(*)+SUM(GREATEST(q1.reruns, 0)) as Runs, FORMAT(avgbottom, 1) as Avg Bottom, FORMAT(3*stdbottom, 2) as 3 Sig, FORMAT(maxbottom, 1) as Max, FORMAT(minbottom, 1) as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, CONCAT(FORMAT(100*SUM(numbottoms)/SUM(numonep), 1), ' %') as % Success, FORMAT(3*stdbottom/avgbottom, 2) as 3Sig/Avg, FORMAT(AVG(avgbottom), 1) as 'Wafer Avg', FORMAT(AVG(Wafer3Sigma), 2) as 'Wafer 3 Sigma', FORMAT(AVG(Ranges), 1) as 'Avg Range', FORMAT(3*STD(Ranges), 2) as '3Sig of Ranges', FORMAT(MAX(Ranges), 1) as 'Max Range', FORMAT(MIN(Ranges), 1) as 'Min Range', FORMAT((SUM(numonep) - SUM(numbottoms))/COUNT(*), 1) as 'NAs/Wafer', mx.maxImage as maxImage, mn.minImage as minImage, lr.lrImage as lrlrImage, cm.cmImage as cmcmImage FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE TRUE AND data_cst.target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1 JOIN (SELECT data_cstimage.name as minImage, data_cst.bottom, data_cst.target_name_id, data_cst.date_time, data_cst.lot_id, data_cst.tool_id, data_cst.roiname, data_cst.recipe_id, data_cst.ep FROM data_cstimage, data_cst WHERE data_cstimage.id = data_cst.image_measurer_id) mn ON mn.bottom = q1.minbottom AND mn.target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND mn.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' JOIN (SELECT data_cstimage.name as maxImage, data_cst.bottom, data_cst.target_name_id, data_cst.date_time, data_cst.lot_id, data_cst.tool_id, data_cst.roiname, data_cst.recipe_id, data_cst.ep FROM data_cstimage, data_cst WHERE data_cstimage.id = data_cst.image_measurer_id) mx ON mx.bottom = q1.maxbottom AND mx.target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND mx.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' JOIN (SELECT data_cstimage.name as lrImage, data_cst.bottom, data_cst.target_name_id, data_cst.date_time, data_cst.lot_id, data_cst.tool_id, data_cst.roiname, data_cst.recipe_id, data_cst.ep FROM data_cstimage, data_cst WHERE data_cstimage.id
RE: getting certain rows from a group by
my counts are 3 times too much. Without studying the code, I would guess that there is a JOIN between he data that needs COUNTing and the GROUP BY for the COUNT. That is, it collects more 'joined' rows before counting. Fixing it will probably make the query even messier. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Wednesday, September 19, 2012 3:22 PM To: Rick James Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: Re: getting certain rows from a group by Well, I'm getting the proper rows from the 4 joins I added, but now I'm seeing a weird side effect - my counts are 3 times to much. The query is really huge and nasty now, but I'm going to paste it below. In the outer most select, Wafers, Rerun, Runs, and Count are 3 times what they should be. If I take off the joins, I get the correct counts. I can't see why that's happening. SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, FORMAT(SUM(numonep)/(COUNT(*)+(SUM(GREATEST(q1.reruns, 0, 1) as 'Sites/Wafer', DATE_FORMAT(MAX(LastRun), '%m/%d/%y') as Last Run, SUM(GREATEST(q1.reruns, 0)) as Rerun, COUNT(*)+SUM(GREATEST(q1.reruns, 0)) as Runs, FORMAT(avgbottom, 1) as Avg Bottom, FORMAT(3*stdbottom, 2) as 3 Sig, FORMAT(maxbottom, 1) as Max, FORMAT(minbottom, 1) as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, CONCAT(FORMAT(100*SUM(numbottoms)/SUM(numonep), 1), ' %') as % Success, FORMAT(3*stdbottom/avgbottom, 2) as 3Sig/Avg, FORMAT(AVG(avgbottom), 1) as 'Wafer Avg', FORMAT(AVG(Wafer3Sigma), 2) as 'Wafer 3 Sigma', FORMAT(AVG(Ranges), 1) as 'Avg Range', FORMAT(3*STD(Ranges), 2) as '3Sig of Ranges', FORMAT(MAX(Ranges), 1) as 'Max Range', FORMAT(MIN(Ranges), 1) as 'Min Range', FORMAT((SUM(numonep) - SUM(numbottoms))/COUNT(*), 1) as 'NAs/Wafer', mx.maxImage as maxImage, mn.minImage as minImage, lr.lrImage as lrlrImage, cm.cmImage as cmcmImage FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE TRUE AND data_cst.target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1 JOIN (SELECT data_cstimage.name as minImage, data_cst.bottom, data_cst.target_name_id, data_cst.date_time, data_cst.lot_id, data_cst.tool_id, data_cst.roiname, data_cst.recipe_id, data_cst.ep FROM data_cstimage, data_cst WHERE data_cstimage.id = data_cst.image_measurer_id) mn ON mn.bottom = q1.minbottom AND mn.target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND mn.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' JOIN (SELECT data_cstimage.name as maxImage, data_cst.bottom, data_cst.target_name_id, data_cst.date_time, data_cst.lot_id, data_cst.tool_id, data_cst.roiname, data_cst.recipe_id, data_cst.ep FROM data_cstimage, data_cst WHERE data_cstimage.id = data_cst.image_measurer_id) mx ON mx.bottom = q1.maxbottom AND mx.target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121
Re: getting certain rows from a group by
2012/09/19 14:36 -0400, Larry Martell MIN(ABS(Avg(bottom) - bottom)) Is not valid. It gives: ERROR (HY000): Invalid use of group function Yes, I had my doubts of that, for all that I suggested it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
getting certain rows from a group by
I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Runs, avgbottom as Avg Bottom, 3*stdbottom as 3 Sig, maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success, 3*stdbottom/avgbottom as 3Sig/Avg, AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range', (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer' FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1, data_target WHERE data_target.id = target_name_id GROUP BY q1.target_name_id, q1.ep; This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). Is this even possible from one query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: getting certain rows from a group by
On 2012-09-18 5:53 AM, Larry Martell wrote: I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Runs, avgbottom as Avg Bottom, 3*stdbottom as 3 Sig, maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success, 3*stdbottom/avgbottom as 3Sig/Avg, AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range', (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer' FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1, data_target WHERE data_target.id = target_name_id GROUP BY q1.target_name_id, q1.ep; This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). Is this even possible from one query? Might be, but what's the importance of doing it as one query? I'd start by saving this result to a temp table and developing the new query. When that's running, see if you can to optimise a query built by replacing the reference to the temp table with the original query. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: getting certain rows from a group by
On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 2012-09-18 5:53 AM, Larry Martell wrote: I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Runs, avgbottom as Avg Bottom, 3*stdbottom as 3 Sig, maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success, 3*stdbottom/avgbottom as 3Sig/Avg, AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range', (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer' FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1, data_target WHERE data_target.id = target_name_id GROUP BY q1.target_name_id, q1.ep; This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). Is this even possible from one query? Might be, but what's the importance of doing it as one query? Because it's part of a django based web app, and the class that this is part of only supports having a single query. To use multiple queries will require a fairly major rewrite of the server side of that app. I'd start by saving this result to a temp table and developing the new query. When that's running, see if you can to optimise a query built by replacing the reference to the temp table with the original query. Thanks, I'll look into this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: getting certain rows from a group by
For single query... Plan A: Repeat the SELECT as once for each (MIN, AVG, etc) as a subquery in WHERE bottom = ( SELECT MIN(bottom) ... ) Plan B: FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max Plan C: Get rid of 3rd party packages that eventually get in your way instead of 'helping'. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 4:57 AM To: peter.braw...@earthlink.net Cc: mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 2012-09-18 5:53 AM, Larry Martell wrote: I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Runs, avgbottom as Avg Bottom, 3*stdbottom as 3 Sig, maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success, 3*stdbottom/avgbottom as 3Sig/Avg, AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range', (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer' FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1, data_target WHERE data_target.id = target_name_id GROUP BY q1.target_name_id, q1.ep; This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). Is this even possible from one query? Might be, but what's the importance of doing it as one query? Because it's part of a django based web app, and the class that this is part of only supports having a single query. To use multiple queries will require a fairly major rewrite of the server side of that app. I'd start by saving this result to a temp table and developing the new query. When that's running, see if you can to optimise a query built by replacing the reference to the temp table with the original query. Thanks, I'll look into this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: getting certain rows from a group by
On Tue, Sep 18, 2012 at 7:56 AM, Larry Martell larry.mart...@gmail.com wrote: On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 2012-09-18 5:53 AM, Larry Martell wrote: I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Runs, avgbottom as Avg Bottom, 3*stdbottom as 3 Sig, maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success, 3*stdbottom/avgbottom as 3Sig/Avg, AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range', (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer' FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1, data_target WHERE data_target.id = target_name_id GROUP BY q1.target_name_id, q1.ep; This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). Is this even possible from one query? Might be, but what's the importance of doing it as one query? Because it's part of a django based web app, and the class that this is part of only supports having a single query. To use multiple queries will require a fairly major rewrite of the server side of that app. I'd start by saving this result to a temp table and developing the new query. When that's running, see if you can to optimise a query built by replacing the reference to the temp table with the original query. Thanks, I'll look into this. So by creating a temp table, and then using that in subsequent queries, I can get what I need. But trying to incorporate that into original query will seem to require a bunch of hairy subqueries (and there already is one in the original query). After running the above query into a temp table, rollup, here are the 4 queries I came up with to get what I need: SELECT rollup.Target, rollup.EP, rollup.`Avg Bottom`, data_cst.id, data_cst.bottom FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id = data_cst.target_name_id GROUP BY rollup.Target, rollup.EP ORDER BY ABS(data_cst.bottom - rollup.`Avg Bottom`); SELECT rollup.Target, rollup.EP, rollup.`Last Run`, data_cst.id, data_cst.date_time FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.date_time = rollup.`Last Run` AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id = data_cst.target_name_id; SELECT rollup.Target, rollup.EP, rollup.min, data_cst.id FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.bottom = rollup.min AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id = data_cst.target_name_id; SELECT rollup.Target, rollup.EP, rollup.max, data_cst.id FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.bottom = rollup.max AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id = data_cst.target_name_id; What would be the best way to incorporate these into original query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http
Re: getting certain rows from a group by
On Tue, Sep 18, 2012 at 2:05 PM, Rick James rja...@yahoo-inc.com wrote: For single query... Plan A: Repeat the SELECT as once for each (MIN, AVG, etc) as a subquery in WHERE bottom = ( SELECT MIN(bottom) ... ) Plan B: FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max Can you elaborate on this? I don't see how this will give me what I need. I'm not looking for the min or max bottom - I already have that - I'm looking for the row from each group that has the min and max bottom. Plan C: Get rid of 3rd party packages that eventually get in your way instead of 'helping'. It's not django that is getting in the way. django is a fantastic framework for web development. It's just changing requirements. The original developer (who no longer works here) created a few different classes, and then based on the original requirements, chose to use the one that supports a single query. That worked then, but may not work now. There are other classes that allow multiple queries, but then you have to parse the data and stuff it into the context that will be picked up by the browser yourself. The single query class does that for you. I may yet go that way, but I'm trying to avoid a big rewrite if I can. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 4:57 AM To: peter.braw...@earthlink.net Cc: mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 2012-09-18 5:53 AM, Larry Martell wrote: I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Runs, avgbottom as Avg Bottom, 3*stdbottom as 3 Sig, maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success, 3*stdbottom/avgbottom as 3Sig/Avg, AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range', (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer' FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1, data_target WHERE data_target.id = target_name_id GROUP BY q1.target_name_id, q1.ep; This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). Is this even possible from one query? Might be, but what's the importance of doing it as one query? Because it's part of a django based web app, and the class that this is part of only supports having a single query. To use multiple queries will require a fairly major rewrite of the server side of that app. I'd start by saving this result to a temp table and developing the new query. When that's running, see if you can to optimise a query built by replacing the reference to the temp table with the original query. Thanks, I'll look into this. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: getting certain rows from a group by
SELECT mn.* mx.* FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max That is, the FROM finds the value for the desired row. The JOIN then gets to the rest of the fields. Caveat: If two rows have bottom = @min, it returns both. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 12:54 PM To: Rick James Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 2:05 PM, Rick James rja...@yahoo-inc.com wrote: For single query... Plan A: Repeat the SELECT as once for each (MIN, AVG, etc) as a subquery in WHERE bottom = ( SELECT MIN(bottom) ... ) Plan B: FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max Can you elaborate on this? I don't see how this will give me what I need. I'm not looking for the min or max bottom - I already have that - I'm looking for the row from each group that has the min and max bottom. Plan C: Get rid of 3rd party packages that eventually get in your way instead of 'helping'. It's not django that is getting in the way. django is a fantastic framework for web development. It's just changing requirements. The original developer (who no longer works here) created a few different classes, and then based on the original requirements, chose to use the one that supports a single query. That worked then, but may not work now. There are other classes that allow multiple queries, but then you have to parse the data and stuff it into the context that will be picked up by the browser yourself. The single query class does that for you. I may yet go that way, but I'm trying to avoid a big rewrite if I can. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 4:57 AM To: peter.braw...@earthlink.net Cc: mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 2012-09-18 5:53 AM, Larry Martell wrote: I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Runs, avgbottom as Avg Bottom, 3*stdbottom as 3 Sig, maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success, 3*stdbottom/avgbottom as 3Sig/Avg, AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range', (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer' FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120, 121) AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' GROUP BY target_name_id, ep, wafer_id HAVING count(*) 999) q1, data_target WHERE data_target.id = target_name_id GROUP BY q1.target_name_id, q1.ep; This works fine. But now I need to get a certain column (image_measurer_id) with each row returned that corresponds to the row from the group that has bottom = Min(bottom), bottom = Max(bottom), bottom closest to Avg(bottom), and bottom from the row where date_time = Max(date_time). Is this even possible from one query? Might be, but what's the importance of doing it as one query? Because it's part of a django based web app, and the class that this is part of only supports having
Re: getting certain rows from a group by
On Tue, Sep 18, 2012 at 4:01 PM, Rick James rja...@yahoo-inc.com wrote: SELECT mn.* mx.* FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max That is, the FROM finds the value for the desired row. The JOIN then gets to the rest of the fields. Caveat: If two rows have bottom = @min, it returns both. Thanks a lot Rick! This was super helpful. I've got it working for 3 of the 4 cases - min(bottom), max(bottom), and max(date_time). But I can't figure out how to work in the last case - where bottom is closest to avg(bottom). In an individual query I can get it with an order by, like this: SELECT rollup.Target, rollup.EP, rollup.`Avg Bottom`, data_cst.id, data_cst.bottom FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id = data_cst.target_name_id GROUP BY rollup.Target, rollup.EP ORDER BY ABS(data_cst.bottom - rollup.`Avg Bottom`); Any way to work that into another join? Thanks! -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 12:54 PM To: Rick James Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 2:05 PM, Rick James rja...@yahoo-inc.com wrote: For single query... Plan A: Repeat the SELECT as once for each (MIN, AVG, etc) as a subquery in WHERE bottom = ( SELECT MIN(bottom) ... ) Plan B: FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max Can you elaborate on this? I don't see how this will give me what I need. I'm not looking for the min or max bottom - I already have that - I'm looking for the row from each group that has the min and max bottom. Plan C: Get rid of 3rd party packages that eventually get in your way instead of 'helping'. It's not django that is getting in the way. django is a fantastic framework for web development. It's just changing requirements. The original developer (who no longer works here) created a few different classes, and then based on the original requirements, chose to use the one that supports a single query. That worked then, but may not work now. There are other classes that allow multiple queries, but then you have to parse the data and stuff it into the context that will be picked up by the browser yourself. The single query class does that for you. I may yet go that way, but I'm trying to avoid a big rewrite if I can. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 4:57 AM To: peter.braw...@earthlink.net Cc: mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 2012-09-18 5:53 AM, Larry Martell wrote: I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Runs, avgbottom as Avg Bottom, 3*stdbottom as 3 Sig, maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success, 3*stdbottom/avgbottom as 3Sig/Avg, AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range', (SUM(numonep) - SUM(numbottoms))/COUNT(*) as 'NAs/Wafer' FROM (SELECT target_name_id, ep, wafer_id, COUNT(bottom) as numbottoms, AVG(bottom) as avgbottom, STD(bottom) as stdbottom, MAX(bottom) as maxbottom, MIN(bottom) as minbottom, MAX(date_time) as LastRun, COUNT(*) as numonep, COUNT(DISTINCT target_name_id, ep, lot_id, data_file_id)-1 as reruns, COUNT(DISTINCT(lot_id)) as Lots, 3*STD(bottom) as Wafer3Sigma, MAX(bottom) - MIN(bottom) as Ranges FROM data_cst WHERE target_name_id IN (775, 776, 777, 778, 779, 780, 45, 44, 116, 117, 118, 119, 120
RE: getting certain rows from a group by
SELECT ((the appropriate id)) -- -- FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id = data_cst.target_name_id GROUP BY rollup.Target, rollup.EP ORDER BY ABS(data_cst.bottom - rollup.`Avg Bottom`) LIMIT 1 -- -- -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 2:57 PM To: Rick James Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 4:01 PM, Rick James rja...@yahoo-inc.com wrote: SELECT mn.* mx.* FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max That is, the FROM finds the value for the desired row. The JOIN then gets to the rest of the fields. Caveat: If two rows have bottom = @min, it returns both. Thanks a lot Rick! This was super helpful. I've got it working for 3 of the 4 cases - min(bottom), max(bottom), and max(date_time). But I can't figure out how to work in the last case - where bottom is closest to avg(bottom). In an individual query I can get it with an order by, like this: SELECT rollup.Target, rollup.EP, rollup.`Avg Bottom`, data_cst.id, data_cst.bottom FROM data_cst, rollup, data_target WHERE data_target.name = rollup.Target AND data_cst.ep = rollup.EP AND data_cst.date_time BETWEEN '2010-03-04 00:00:00' AND '2010-03-04 23:59:59' AND data_target.id = data_cst.target_name_id GROUP BY rollup.Target, rollup.EP ORDER BY ABS(data_cst.bottom - rollup.`Avg Bottom`); Any way to work that into another join? Thanks! -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 12:54 PM To: Rick James Cc: peter.braw...@earthlink.net; mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 2:05 PM, Rick James rja...@yahoo-inc.com wrote: For single query... Plan A: Repeat the SELECT as once for each (MIN, AVG, etc) as a subquery in WHERE bottom = ( SELECT MIN(bottom) ... ) Plan B: FROM ( SELECT @min = MIN(bottom), @max = MAX(bottom), ... ) JOIN data_cst mn ON bottom = @min JOIN data_cst mx ON bottom = @max Can you elaborate on this? I don't see how this will give me what I need. I'm not looking for the min or max bottom - I already have that - I'm looking for the row from each group that has the min and max bottom. Plan C: Get rid of 3rd party packages that eventually get in your way instead of 'helping'. It's not django that is getting in the way. django is a fantastic framework for web development. It's just changing requirements. The original developer (who no longer works here) created a few different classes, and then based on the original requirements, chose to use the one that supports a single query. That worked then, but may not work now. There are other classes that allow multiple queries, but then you have to parse the data and stuff it into the context that will be picked up by the browser yourself. The single query class does that for you. I may yet go that way, but I'm trying to avoid a big rewrite if I can. -Original Message- From: Larry Martell [mailto:larry.mart...@gmail.com] Sent: Tuesday, September 18, 2012 4:57 AM To: peter.braw...@earthlink.net Cc: mysql@lists.mysql.com Subject: Re: getting certain rows from a group by On Tue, Sep 18, 2012 at 7:52 AM, Peter Brawley peter.braw...@earthlink.net wrote: On 2012-09-18 5:53 AM, Larry Martell wrote: I have this query: SELECT data_target.name as Target, q1.ep as EP, COUNT(*) as Wafers, Lots, SUM(numonep)/(COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END)) as 'Sites/Wafer', MAX(LastRun) as Last Run, SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Rerun, COUNT(*)+SUM(CASE WHEN reruns 0 THEN reruns ELSE 0 END) as Runs, avgbottom as Avg Bottom, 3*stdbottom as 3 Sig, maxbottom as Max, minbottom as Min, SUM(numonep) as Count, SUM(numonep) - SUM(numbottoms) as NAs, 100-((SUM(numonep) - SUM(numbottoms))/SUM(numonep)*100) as % Success, 3*stdbottom/avgbottom as 3Sig/Avg, AVG(avgbottom) as 'Wafer Avg', AVG(Wafer3Sigma) as 'Wafer 3 Sigma', AVG(Ranges) as 'Avg Range', 3*STD(Ranges) as '3Sig of Ranges', MAX(Ranges) as 'Max Range', MIN(Ranges) as 'Min Range
Re: Group expansion as part of the result
In the last episode (Mar 27), Paul Halliday said: Say I have: SELECT COUNT(name), name, COUNT(DISTINCT(status)) FROM table GROUP BY name and it returns: 20 paul 5 19 john 2 75 mark 3 is there a way to return what comprises DISTINCT(status) as part of the result? so: 20 paul 2,3,1,20,9 19 john 20,9 75 mark 1,20,9 You want GROUP_CONCAT: http://dev.mysql.com/doc/refman/5.5/en/group-by-functions.html#function_group-concat -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Group expansion as part of the result
On Tue, Mar 27, 2012 at 3:43 PM, Dan Nelson dnel...@allantgroup.com wrote: In the last episode (Mar 27), Paul Halliday said: Say I have: SELECT COUNT(name), name, COUNT(DISTINCT(status)) FROM table GROUP BY name and it returns: 20 paul 5 19 john 2 75 mark 3 is there a way to return what comprises DISTINCT(status) as part of the result? so: 20 paul 2,3,1,20,9 19 john 20,9 75 mark 1,20,9 You want GROUP_CONCAT: Heh, and exactly how I wanted it formatted. Great! Thanks. -- Paul Halliday http://www.squertproject.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: strange select/join/group by with rollup issue....
I'm not sure your method isn't working, but try changing changing the to date part to '2012-02-08' and see what you get. HTH, Arthur
Re: strange select/join/group by with rollup issue....
Thanks, it seems to be working now. I just discovered WITH ROLLUP. It made me very happy on this project... On 2/8/12 2:54 AM, Arthur Fuller wrote: I'm not sure your method isn't working, but try changing changing the to date part to '2012-02-08' and see what you get. HTH, Arthur -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
strange select/join/group by with rollup issue....
I am having a problem with select results that I don't understand. It seems to be tied up with a GROUP BY statement. Forgive the complexity of the SQL, I inherited some problematic data structuring. If I use this statement: SELECT lu_rcode_bucket.bucket AS 'BUCKET', CP_PKG.value AS 'PRODUCT', CP_PKG.value 'PACKAGE', client.active AS 'ACTIVE', client.created AS 'CREATED', count(*) as 'CNT' FROM client JOIN client_profile CP_RCODE ON client.acnt = CP_RCODE.acnt AND CP_RCODE.item = 'rcode' JOIN lu_rcode_bucket ON INSTR(CP_RCODE.value, lu_rcode_bucket.prefix) = 1 JOIN client_profile CP_PKG ON client.acnt = CP_PKG.acnt AND CP_PKG.item = 'pkg' LEFT JOIN client_profile CP_IDX_PKG ON client.acnt = CP_IDX_PKG.acnt AND CP_IDX_PKG.item = 'IDX_PKG' WHERE client.created = '2012-02-07' AND client.created = '2012-02-07' GROUP BY BUCKET, PRODUCT, PACKAGE, active with ROLLUP I get what I expect, having a number of rows where the client.created date is 2012-02-07. But if I change it to this (the only change is the from date): SELECT lu_rcode_bucket.bucket AS 'BUCKET', CP_PKG.value AS 'PRODUCT', CP_IDX_PKG.value 'PACKAGE', client.active AS 'ACTIVE', client.created AS 'CREATED', count(*) as 'CNT' FROM client JOIN client_profile CP_RCODE ON client.acnt = CP_RCODE.acnt AND CP_RCODE.item = 'rcode' JOIN lu_rcode_bucket ON INSTR(CP_RCODE.value, lu_rcode_bucket.prefix) = 1 JOIN client_profile CP_PKG ON client.acnt = CP_PKG.acnt AND CP_PKG.item = 'pkg' LEFT JOIN client_profile CP_IDX_PKG ON client.acnt = CP_IDX_PKG.acnt AND CP_IDX_PKG.item = 'IDX_PKG' WHERE client.created = '2012-02-01' AND client.created = '2012-02-07' GROUP BY BUCKET, PRODUCT, PACKAGE, active with ROLLUP The results contain no data with client.created = 2012-02-07. If I get rid of the group by (and the count(*)), there are rows with all 7 dates. I have tried changing the to date from '2012-02-07' to '2012-02-08', in case this was a less than issue, but that doesn't change. Why is the group by dropping the last date of my data? thanks, andy -- Andy Wallace iHOUSEweb, Inc. awall...@ihouseweb.com (866) 645-7700 ext 219 -- There are two ways to build software: Make it so simple that there are obviously no bugs, or make it so complex that there are no obvious bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Within-group aggregate query help please - customers and latest subscription row
2011/10/24 16:31 -0700, Daevid Vincent WHERE cs.customer_id = 7 GROUP BY customer_id Well, the latter line is now redundant. How will you make the '7' into a parameter? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Within-group aggregate query help please - customers and latest subscription row
I know this is a common problem, and I've been struggling with it for a full day now but I can't get it. I also tried a few sites for examples: http://www.artfulsoftware.com/infotree/queries.php#101 http://forums.devarticles.com/general-sql-development-47/select-max-datetime -problem-10210.html Anyways, pretty standard situation: CREATE TABLE `customers` ( `customer_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(64) NOT NULL default '', `name` varchar(128) NOT NULL default '', `username` varchar(32) NOT NULL, ... ); CREATE TABLE `customers_subscriptions` ( `subscription_id` bigint(12) unsigned NOT NULL default '0', `customer_id` int(10) unsigned NOT NULL default '0', `date` date NOT NULL default '-00-00', ... ); I want to show a table where I list out the ID, email, username, and LAST SUBSCRIPTION. I need this data in TWO ways: The FIRST way, is with a query JOINing the two tables so that I can easily display that HTML table mentioned. That is ALL customers and the latest subscription they have. The SECOND way is when I drill into the customer, I already know the customer_id and so don't need to JOIN with that table, I just want to get the proper row from the customers_subscriptions table itself. SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY `date` DESC; subscription_id processor customer_id date --- - --- -- 134126370 chargem 7 2005-08-04 1035167192 billme 7 2004-02-08 SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; gives me 2005-08-04 obviously, but as you all know, mySQL completely takes a crap on your face when you try what would seem to be the right query: SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; subscription_id MAX(`date`) --- --- 1035167192 2005-08-04 Notice how I have the correct DATE, but the wrong subscription_id. In the example web sites above, they seem to deal more with finding the MAX(subscription_id), which in my case will not work. I need the max DATE and the corresponding row (with matching subscription_id). Thanks, d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Within-group aggregate query help please - customers and latest subscription row
A kind (and shy) soul replied to me off list and suggested this solution, however, this takes 28 seconds (that's for a single customer_id, so this is not going to scale). Got any other suggestions? :-) SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs GROUP BY customer_id) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate WHERE c.customer_id = 7; There are 781,270 customers (nearly 1 million) and 1,018,092 customer_subscriptions. Our tables have many indexes on pretty much every column and for sure the ones we use here. EXPLAIN says: id select_type table typepossible_keys key key_len refrows Extra -- --- -- -- --- --- -- --- --- 1 PRIMARY c const PRIMARY PRIMARY 4 const 1 1 PRIMARY s ref date,customer_id customer_id 4 const 2 1 PRIMARY derived2 ALL (NULL)(NULL) (NULL) (NULL) 781265 Using where 2 DERIVED cs ALL (NULL)(NULL) (NULL) (NULL) 1018092 Using temporary; Using filesort -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, October 24, 2011 1:46 PM To: mysql@lists.mysql.com Subject: Within-group aggregate query help please - customers and latest subscription row I know this is a common problem, and I've been struggling with it for a full day now but I can't get it. I also tried a few sites for examples: http://www.artfulsoftware.com/infotree/queries.php#101 http://forums.devarticles.com/general-sql-development-47/select-max-datetime -problem-10210.html Anyways, pretty standard situation: CREATE TABLE `customers` ( `customer_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(64) NOT NULL default '', `name` varchar(128) NOT NULL default '', `username` varchar(32) NOT NULL, ... ); CREATE TABLE `customers_subscriptions` ( `subscription_id` bigint(12) unsigned NOT NULL default '0', `customer_id` int(10) unsigned NOT NULL default '0', `date` date NOT NULL default '-00-00', ... ); I want to show a table where I list out the ID, email, username, and LAST SUBSCRIPTION. I need this data in TWO ways: The FIRST way, is with a query JOINing the two tables so that I can easily display that HTML table mentioned. That is ALL customers and the latest subscription they have. The SECOND way is when I drill into the customer, I already know the customer_id and so don't need to JOIN with that table, I just want to get the proper row from the customers_subscriptions table itself. SELECT * FROM `customers_subscriptions` WHERE customer_id = 7 ORDER BY `date` DESC; subscription_id processor customer_id date --- - --- -- 134126370 chargem 7 2005-08-04 1035167192 billme 7 2004-02-08 SELECT MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; gives me 2005-08-04 obviously, but as you all know, mySQL completely takes a crap on your face when you try what would seem to be the right query: SELECT subscription_id, MAX(`date`) FROM `customers_subscriptions` WHERE customer_id = 7 GROUP BY customer_id; subscription_id MAX(`date`) --- --- 1035167192 2005-08-04 Notice how I have the correct DATE, but the wrong subscription_id. In the example web sites above, they seem to deal more with finding the MAX(subscription_id), which in my case will not work. I need the max DATE and the corresponding row (with matching subscription_id). Thanks, d -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Within-group aggregate query help please - customers and latest subscription row
Okay, it seems I am learning... slowly... So there needs to be a second WHERE in the sub-select... To get ONE customer's last subscription (0.038s): SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs WHERE cs.customer_id = 7 GROUP BY customer_id ) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate WHERE c.customer_id = 7; To get ALL customers and their last subscription row (1m:28s) SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs GROUP BY customer_id ) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate ORDER BY customer_id LIMIT 10; Thanks to you know who you are for pointing me in the right direction. Hopefully this helps someone else. d. -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, October 24, 2011 4:06 PM To: mysql@lists.mysql.com Subject: RE: Within-group aggregate query help please - customers and latest subscription row A kind (and shy) soul replied to me off list and suggested this solution, however, this takes 28 seconds (that's for a single customer_id, so this is not going to scale). Got any other suggestions? :-) SELECT c.customer_id, c.email, c.name, c.username, s.subscription_id, s.`date` FROM customers AS c INNER JOIN customers_subscriptions AS s ON c.customer_id = s.customer_id INNER JOIN (SELECT MAX(`date`) AS LastDate, customer_id FROM customers_subscriptions AS cs GROUP BY customer_id) AS `x` ON s.customer_id = x.customer_id AND s.date = x.LastDate WHERE c.customer_id = 7; There are 781,270 customers (nearly 1 million) and 1,018,092 customer_subscriptions. Our tables have many indexes on pretty much every column and for sure the ones we use here. EXPLAIN says: id select_type table typepossible_keys key key_len refrows Extra -- --- -- -- --- --- -- --- --- 1 PRIMARY c const PRIMARY PRIMARY 4 const 1 1 PRIMARY s ref date,customer_id customer_id 4 const 2 1 PRIMARY derived2 ALL (NULL)(NULL) (NULL) (NULL) 781265 Using where 2 DERIVED cs ALL (NULL)(NULL) (NULL) (NULL) 1018092 Using temporary; Using filesort -Original Message- From: Daevid Vincent [mailto:dae...@daevid.com] Sent: Monday, October 24, 2011 1:46 PM To: mysql@lists.mysql.com Subject: Within-group aggregate query help please - customers and latest subscription row I know this is a common problem, and I've been struggling with it for a full day now but I can't get it. I also tried a few sites for examples: http://www.artfulsoftware.com/infotree/queries.php#101 http://forums.devarticles.com/general-sql-development-47/select-max-datetime -problem-10210.html Anyways, pretty standard situation: CREATE TABLE `customers` ( `customer_id` int(10) unsigned NOT NULL auto_increment, `email` varchar(64) NOT NULL default '', `name` varchar(128) NOT NULL default '', `username` varchar(32) NOT NULL, ... ); CREATE TABLE `customers_subscriptions` ( `subscription_id` bigint(12) unsigned NOT NULL default '0', `customer_id` int(10) unsigned NOT NULL default '0', `date` date NOT NULL default '-00-00', ... ); I want to show a table where I list out the ID, email, username, and LAST SUBSCRIPTION. I need this data in TWO ways: The FIRST way, is with a query JOINing the two tables so that I can easily display that HTML table mentioned. That is ALL customers and the latest subscription they have. The SECOND way
Partial Index with group by
I am trying to optimize a query that uses a group by on a varchar(255) column. The column has a large enough cardinality that a 10 character partial index uniquely covers over 99% of all values. I was hoping that this partial index would be able to help with the group by (though obviously not as much as a full index), but it seems that only full indexes can be used with group by optimizations. Would anyone be able confirm this one way or another? I am using 5.1.45 with InnoDB Storage Engine. I ended up just adding the full index for the performance, but would like to know what's going on for my personal knowledge. Any insights would be great. Thanks. Les Fletcher -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I can't have group as a column name in a table? [SOLVED]
Aveek, Simcha, Johan, Thanks for explaining the situation. I knew there were some reserved words, but I hadn't realized there were so many. Anyway, now that I know I can protect my column names with backticks, I'm good to go. -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
I can't have group as a column name in a table?
MySQL users, Simple question: In one table in my database, the column was named group. I kept getting failed query errors until I renamed the column. I've never before encountered a situation where MySQL mistook a column name for part of the query syntax. Should I never use the word group for column names? Seems a little silly. Is there a way to protect column names to that there is no confusion? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I can't have group as a column name in a table?
Use a quote around the column name or explicitly specify the column as table.column (as for e.g. mytable.group) in the query. For more details refer to http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html Thanks Aveek On Feb 24, 2011, at 4:36 PM, Dave M G wrote: MySQL users, Simple question: In one table in my database, the column was named group. I kept getting failed query errors until I renamed the column. I've never before encountered a situation where MySQL mistook a column name for part of the query syntax. Should I never use the word group for column names? Seems a little silly. Is there a way to protect column names to that there is no confusion? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com
Re: I can't have group as a column name in a table?
Have you read about reserved words in MySql? http://dev.mysql.com/doc/refman/5.1/en/reserved-words.html -- João Cândido de Souza Neto Dave M G d...@articlass.org escreveu na mensagem news:4d663ba0.5090...@articlass.org... MySQL users, Simple question: In one table in my database, the column was named group. I kept getting failed query errors until I renamed the column. I've never before encountered a situation where MySQL mistook a column name for part of the query syntax. Should I never use the word group for column names? Seems a little silly. Is there a way to protect column names to that there is no confusion? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I can't have group as a column name in a table?
On Thu, 24 Feb 2011 16:43:56 +0530 Aveek Misra ave...@yahoo-inc.com wrote: Use a quote around the column name or explicitly specify the column as table.column (as for e.g. mytable.group) in the query. For more details refer to http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html use backticks, not quotes. `group`, not 'group'. -- Simcha Younger sim...@syounger.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I can't have group as a column name in a table?
On Thu, Feb 24, 2011 at 12:06 PM, Dave M G d...@articlass.org wrote: Should I never use the word group for column names? Seems a little silly. Is there a way to protect column names to that there is no confusion? As several people already pointed out, simply use backticks. Simple quotes have started to work in more and more places in newer versions of MySQL. However, it is considered bad form to name columns for reserved words - even ones as obvious as group or index. Bad form in the same way that you wouldn't name any variables define or if while programming; or in a very similar way that you wouldn't put a box of TNT next to a burning candle - it's an accident waiting to happen. The escapes are there in case an upgrade creates new reserved words that you've already used in column names - partition comes to mind - but if you are still in a phase where you can avoid using reserved words, please spare yourself and others a lot of trouble and do so; even if only because while you can fix your code, you can't fix someone else's - think management tools, backup scripts, whatever may touch the db in the future. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: I can't have group as a column name in a table?
The best comparison I´ve never seen in my life was the TNT. LOL -- João Cândido de Souza Neto Johan De Meersman vegiv...@tuxera.be escreveu na mensagem news:AANLkTikPeVuTpj9E0iepFncCJZQOF6sn_dbrhp0=p...@mail.gmail.com... On Thu, Feb 24, 2011 at 12:06 PM, Dave M G d...@articlass.org wrote: Should I never use the word group for column names? Seems a little silly. Is there a way to protect column names to that there is no confusion? As several people already pointed out, simply use backticks. Simple quotes have started to work in more and more places in newer versions of MySQL. However, it is considered bad form to name columns for reserved words - even ones as obvious as group or index. Bad form in the same way that you wouldn't name any variables define or if while programming; or in a very similar way that you wouldn't put a box of TNT next to a burning candle - it's an accident waiting to happen. The escapes are there in case an upgrade creates new reserved words that you've already used in column names - partition comes to mind - but if you are still in a phase where you can avoid using reserved words, please spare yourself and others a lot of trouble and do so; even if only because while you can fix your code, you can't fix someone else's - think management tools, backup scripts, whatever may touch the db in the future. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I can't have group as a column name in a table?
At 05:13 AM 2/24/2011, you wrote: Use a quote around the column name or explicitly specify the column as table.column (as for e.g. mytable.group) in the query. For more details refer to http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html Thanks Aveek Hmmm. Everyone has given me a great idea. I am going to change my table names to Table, Group, Having, Select, Into, Order By, Update, Delete etc. just to confuse hackers so they won't be able to launch a sql injection attack against my website. The naming convention will drive them crazy. Mike (Just kidding) On Feb 24, 2011, at 4:36 PM, Dave M G wrote: MySQL users, Simple question: In one table in my database, the column was named group. I kept getting failed query errors until I renamed the column. I've never before encountered a situation where MySQL mistook a column name for part of the query syntax. Should I never use the word group for column names? Seems a little silly. Is there a way to protect column names to that there is no confusion? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: I can't have group as a column name in a table?
LOL -- João Cândido de Souza Neto mos mo...@fastmail.fm escreveu na mensagem news:6.0.0.22.2.20110224093057.044a0...@mail.messagingengine.com... At 05:13 AM 2/24/2011, you wrote: Use a quote around the column name or explicitly specify the column as table.column (as for e.g. mytable.group) in the query. For more details refer to http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html Thanks Aveek Hmmm. Everyone has given me a great idea. I am going to change my table names to Table, Group, Having, Select, Into, Order By, Update, Delete etc. just to confuse hackers so they won't be able to launch a sql injection attack against my website. The naming convention will drive them crazy. Mike (Just kidding) On Feb 24, 2011, at 4:36 PM, Dave M G wrote: MySQL users, Simple question: In one table in my database, the column was named group. I kept getting failed query errors until I renamed the column. I've never before encountered a situation where MySQL mistook a column name for part of the query syntax. Should I never use the word group for column names? Seems a little silly. Is there a way to protect column names to that there is no confusion? -- Dave M G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Group By Problem
Dear all, I stuck around one more simple problem today. I have a table named *event_loc* having below data : ( It has many columns but I show you main columns that I needed ) _*Tables Data :-*_ *source_idevent_text* 1233 meet 1233 meet 1345ends 1345ends performs 13456 Minister 1233Argentina 1233meet 1345ends 1555is As described above there are more than 10 rows and I want my output as : _*Output Needed :-*_ *source_id event_text Count* 1233 meet 3 1233 Argentina 1 1345 ends3 performs 1 13456 Minister 1 I tried the below query : *select source_id ,event_text,count(*) from event_loc group by source_id,obj_text ;* But displays only unique record_id rows but I want as mentioned output. Failed to achieve the mentioned output. Thanks Regards Adarsh Sharma
RE: Group By Problem
You need to group by event_text, not obj_text: select source_id ,event_text,count(*) from event_loc group by source_id,event_text; Date: Tue, 8 Feb 2011 16:31:39 +0530 From: adarsh.sha...@orkash.com To: mysql@lists.mysql.com Subject: Group By Problem Dear all, I stuck around one more simple problem today. I have a table named *event_loc* having below data : ( It has many columns but I show you main columns that I needed ) _*Tables Data :-*_ *source_id event_text* 1233 meet 1233 meet 1345 ends 1345 ends performs 13456 Minister 1233 Argentina 1233 meet 1345 ends 1555 is As described above there are more than 10 rows and I want my output as : _*Output Needed :-*_ *source_id event_text Count* 1233 meet 3 1233 Argentina 1 1345 ends 3 performs 1 13456 Minister 1 I tried the below query : *select source_id ,event_text,count(*) from event_loc group by source_id,obj_text ;* But displays only unique record_id rows but I want as mentioned output. Failed to achieve the mentioned output. Thanks Regards Adarsh Sharma
Re: Group by question
On 2011-1-16 20:22, Jørn Dahl-Stamnes wrote: Hello, I got a table that store information about which photo-albums that a client is viewing. I want to get the N last visited albums and use the query: mysql select album_id, updated_at, created_at from album_stats order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 51 | 2011-01-16 13:03:04 | 2011-01-16 13:03:04 | | 10 | 2011-01-16 12:20:39 | 2011-01-16 12:20:39 | |2 | 2011-01-16 12:20:35 | 2011-01-16 12:20:35 | | 81 | 2011-01-16 12:20:34 | 2011-01-16 12:20:34 | | 97 | 2011-01-16 11:25:03 | 2011-01-16 11:19:05 | | 81 | 2011-01-16 11:19:04 | 2011-01-16 11:19:04 | |2 | 2011-01-16 11:19:02 | 2011-01-16 11:19:02 | | 10 | 2011-01-16 11:18:58 | 2011-01-16 11:13:04 | +--+-+-+ 8 rows in set (0.09 sec) The problem is that album_id 81 is occuring two times in the list. So I thought I should add a group by in the query: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 278 | 2011-01-13 14:02:50 | 2011-01-13 14:02:00 | | 281 | 2011-01-11 16:41:16 | 2011-01-11 16:35:41 | | 276 | 2010-12-15 14:42:57 | 2010-12-15 14:42:57 | | 275 | 2010-12-15 14:42:48 | 2010-12-15 14:42:48 | | 269 | 2010-09-11 14:09:10 | 2010-09-11 14:09:10 | | 271 | 2010-09-11 14:02:27 | 2010-09-11 14:02:27 | | 273 | 2010-09-11 13:59:06 | 2010-09-11 13:59:06 | | 270 | 2010-09-11 13:57:25 | 2010-09-11 13:57:25 | +--+-+-+ 8 rows in set (0.23 sec) But the result is not what I expected. What have I missed? perhaps i think first you need to retrieve the max(updated_at) group by album_id sets select album_id, updated_at, created_at from album_stats where updated_at in (select max(updated_at) from album_stats group by album_id) limit 8; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Group by question
On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; I believe that your problem is that the group by happens before the order by. Since you're grouping, the updated_at column is not deterministic. If there are multiple rows per album_id, any one of those rows could provide the updated_at column that you're then using to order by. What you probably want is to select (and order by) the max(updated_at). Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Group by question
On Monday 17 January 2011 09:53, Steve Meyers wrote: On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; I believe that your problem is that the group by happens before the order by. Since you're grouping, the updated_at column is not deterministic. If there are multiple rows per album_id, any one of those rows could provide the updated_at column that you're then using to order by. What you probably want is to select (and order by) the max(updated_at). moving the group by to after order by will result in an error: mysql select album_id, updated_at, created_at from album_stats order by updated_at group by album_id desc limit 8; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by album_id desc limit 8' at line 1 Beside, since I'm using Propel and Creole for ORM and database abstraction, I would never be able to change the order of them. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Group by question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 He meant the execution order, please use the agregation function as suggested. On 11-01-17 05:03, Jørn Dahl-Stamnes wrote: On Monday 17 January 2011 09:53, Steve Meyers wrote: On 1/16/11 5:22 AM, Jørn Dahl-Stamnes wrote: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; I believe that your problem is that the group by happens before the order by. Since you're grouping, the updated_at column is not deterministic. If there are multiple rows per album_id, any one of those rows could provide the updated_at column that you're then using to order by. What you probably want is to select (and order by) the max(updated_at). moving the group by to after order by will result in an error: mysql select album_id, updated_at, created_at from album_stats order by updated_at group by album_id desc limit 8; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by album_id desc limit 8' at line 1 Beside, since I'm using Propel and Creole for ORM and database abstraction, I would never be able to change the order of them. -BEGIN PGP SIGNATURE- Version: GnuPG/MacGPG2 v2.0.14 (Darwin) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/ iQEcBAEBAgAGBQJNNHQIAAoJENgwSj9ZOOwrR9UH/RDLojUwXYB1a+fcS0kuwzFW AZ/VdwknydB5ENkt7+MmWgHdVcPwrChE+nY2bpRI9LYp8ehUNwaeC2uV/ToWXFti ZTnVdnFDanHy20UOF3a1X7UXW89/zTy/B06X7NP1NqmIGnAahPK6VBuIx1OP/oGZ +es+m9BIYnuc8JzfRo5YSQuydfWIJ87ygrkodhM/C2VPBWDMwpEX/wuxgW/x+ukM RXKaxHrHOrWc1hWLFp3P+QI+J7VNP1fh6Rxw1Q91latJkY4I3hbN9nEsXeHlD2l2 f5ZNn4LwPPNC++XpGaDqQmA0W2Sua9lHUhtVsxsJt5kIigAwNJ5GnaayY/p9apk= =7MLg -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Group by question
Hello, I got a table that store information about which photo-albums that a client is viewing. I want to get the N last visited albums and use the query: mysql select album_id, updated_at, created_at from album_stats order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 51 | 2011-01-16 13:03:04 | 2011-01-16 13:03:04 | | 10 | 2011-01-16 12:20:39 | 2011-01-16 12:20:39 | |2 | 2011-01-16 12:20:35 | 2011-01-16 12:20:35 | | 81 | 2011-01-16 12:20:34 | 2011-01-16 12:20:34 | | 97 | 2011-01-16 11:25:03 | 2011-01-16 11:19:05 | | 81 | 2011-01-16 11:19:04 | 2011-01-16 11:19:04 | |2 | 2011-01-16 11:19:02 | 2011-01-16 11:19:02 | | 10 | 2011-01-16 11:18:58 | 2011-01-16 11:13:04 | +--+-+-+ 8 rows in set (0.09 sec) The problem is that album_id 81 is occuring two times in the list. So I thought I should add a group by in the query: mysql select album_id, updated_at, created_at from album_stats group by album_id order by updated_at desc limit 8; +--+-+-+ | album_id | updated_at | created_at | +--+-+-+ | 278 | 2011-01-13 14:02:50 | 2011-01-13 14:02:00 | | 281 | 2011-01-11 16:41:16 | 2011-01-11 16:35:41 | | 276 | 2010-12-15 14:42:57 | 2010-12-15 14:42:57 | | 275 | 2010-12-15 14:42:48 | 2010-12-15 14:42:48 | | 269 | 2010-09-11 14:09:10 | 2010-09-11 14:09:10 | | 271 | 2010-09-11 14:02:27 | 2010-09-11 14:02:27 | | 273 | 2010-09-11 13:59:06 | 2010-09-11 13:59:06 | | 270 | 2010-09-11 13:57:25 | 2010-09-11 13:57:25 | +--+-+-+ 8 rows in set (0.23 sec) But the result is not what I expected. What have I missed? -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Query Stored Index instead of Group By
Hi Johan, On Sun, Dec 19, 2010 at 7:11 PM, Johan De Meersman vegiv...@tuxera.bewrote: You can't query the index directly, but if you select only fields that are in the index, no table lookups will be performed - this is called a covering index. Great.. Thanks for the confirmation. Regards, Feris -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Query Stored Index instead of Group By
On Sun, Dec 19, 2010 at 3:19 AM, Feris Thia milis.datab...@phi-integration.com wrote: Hi Everyone, Is there a way to query values stored in our index instead of using group by selection which will produce same results ? You can't query the index directly, but if you select only fields that are in the index, no table lookups will be performed - this is called a covering index. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Query Stored Index instead of Group By
Hi Everyone, Is there a way to query values stored in our index instead of using group by selection which will produce same results ? Please advice.. Regards, Feris
GROUP BY - INNER JOIN and LIMIT - how to get result
I have tables: CREATE TABLE `tblNames` ( ` IdName` int(11) NOT NULL AUTO_INCREMENT, `Name` varchar(60) DEFAULT NULL, PRIMARY KEY (`IdName`), ) ENGINE=MyISAM CREATE TABLE `tblStatusy` ( `IdStatus` int(11) NOT NULL AUTO_INCREMENT, `IdName` int(11) DEFAULT NULL, `Status` varchar(60) DEFAULT NULL, `Data` datetime DEFAULT NULL, PRIMARY KEY (`IdStatus`), KEY `ixIDName` (`IdName `) ) ENGINE=MyISAM How to get result which will be look like this: tblNames.Id, tblNames.Name, (subquery which will return tblStatusy.Status, tblStatusy.Data ordered by Data DESC LIMIT 1), (subquery which will return tblStatusy.Status, tblStatusy.Data ordered by Data DESC LIMIT 1,1), (subquery which will return tblStatusy.Status, tblStatusy.Data ordered by Data DESC LIMIT 2,1) Any idea how to get this? Best regards
RE: query results group/summed by interval
Hi all, Aveeks solution should work if you have at least one call for each intervall. It's the classical GROUP BY solution that only works on the available dataset. Although it should work pretty well in the cited scenario, you will miss intervals (from a all intervals report point of view) if indeed there are intervals (of more than 5 minutes, in this example) when there were no calls at all. I had a somewhat similar problem (running the second scenario, though) and this is the solution I setup (this was a Data Warehouse and that's why you'll read about partition pruning, dataset was dozens of Gigs): http://gpshumano.blogs.dri.pt/2009/09/28/finding-for-each-time-interval-how-many-records-are-ocurring-during-that-interval/ This might become handy if Ghulam understands the differences between my scenario and his. Hope that helps, -NT Quoting Martin Gainty mgai...@hotmail.com: no that would give you the count for each second interval instead of using the interval variable 5 Aveeks floor: FLOOR(X) Returns the largest integer value not greater than X. 1st (seconds/5) interval example 5/5=1 floor(5/5) = 1 supplied value would truncate and give you the int not greater than X then multiply by 5 1*5=5 is correct Aveeks sum function: SUM([DISTINCT] expr) Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used in MySQL 5.0 to sum only the distinct values of expr. SUM() returns NULL if there were no matching rows. sum(calls) from calls group by 5 * floor(seconds/5) sum(calls) from calls group by 5 * floor(5/5) sum(calls) from class group by 5 * 1 sum(calls) from class group by 5 is correct 2nd(seconds/5) interval example 10/5=2 floor(10/5)=2 supplied value would truncate and give you the int not greater than X then multiply by 5 2*5=10 is correct Aveeks sum function sum(calls) from calls group by 5 * floor(seconds/5) sum(calls) from calls group by 5 * floor(10/5) sum(calls) from class group by 5 * 2 sum(calls) from class group by 10 would be applicable only if the interval was 10 Aveek if your interval is 5 change: sum(calls) from calls group by 5 * floor(seconds/5) to sum(calls) from calls group by floor(seconds/5) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Sat, 31 Jul 2010 10:31:43 +0700 From: cuong.m...@vienthongso.com To: ave...@yahoo-inc.com CC: mustafa...@gmail.com; mysql@lists.mysql.com Subject: Re: query results group/summed by interval Hi Aveek, I think Ghulam just want to count calls for each intervals so the query should looks like this: select count(*) as total_calls, queue_seconds from calls group by queue_seconds order by total_calls; - Original Message - From: Aveek Misra ave...@yahoo-inc.com To: Ghulam Mustafa mustafa...@gmail.com, mysql@lists.mysql.com Sent: Tuesday, July 27, 2010 5:54:13 PM Subject: RE: query results group/summed by interval try this ... select 5 * floor(seconds/5) as start, 5 * floor(seconds/5) + 5 as end, sum(calls) from calls group by 5 * floor(seconds/5); This should give you an output of the type +---+--++ | start | end | sum(calls) | +---+--++ | 0 | 5 | 387 | | 5 | 10 | 225 | | 10 | 15 | 74 | +---+--++ Thanks Aveek From: Ghulam Mustafa [mustafa...@gmail.com] Sent: Tuesday, July 27, 2010 3:53 PM To: mysql@lists.mysql.com Subject: query results group/summed by interval Hi everyone, i have two columns (seconds, number of calls), i need to produce a report which will show total number of calls in intervals (let'say 10 seconds interval), i know i can do this programmability in my script but i was wondering if it's possible to accomplish this behavior within mysql. for example i have following data
RE: query results group/summed by interval
You could also pre-define your intervals in a subquery using UNION and join that to your original table like so: select ifnull(sum(calls), 0) as calls, n as queue_seconds from (select 0 as n union select 5 union select 10 union select 15) as step left join calls on calls.queue_seconds (step.n - 5) and calls.queue_seconds = step.n group by n; +---+---+ | calls | queue_seconds | +---+---+ | 250 | 0 | | 168 | 5 | | 268 | 10 | | 0 | 15 | +---+---+ 4 rows in set (0.00 sec) -Travis Date: Sun, 1 Aug 2010 13:16:36 +0100 From: nuno.tava...@dri.pt To: mgai...@hotmail.com CC: cuong.m...@vienthongso.com; ave...@yahoo-inc.com; mustafa...@gmail.com; mysql@lists.mysql.com Subject: RE: query results group/summed by interval Hi all, Aveeks solution should work if you have at least one call for each intervall. It's the classical GROUP BY solution that only works on the available dataset. Although it should work pretty well in the cited scenario, you will miss intervals (from a all intervals report point of view) if indeed there are intervals (of more than 5 minutes, in this example) when there were no calls at all. I had a somewhat similar problem (running the second scenario, though) and this is the solution I setup (this was a Data Warehouse and that's why you'll read about partition pruning, dataset was dozens of Gigs): http://gpshumano.blogs.dri.pt/2009/09/28/finding-for-each-time-interval-how-many-records-are-ocurring-during-that-interval/ This might become handy if Ghulam understands the differences between my scenario and his. Hope that helps, -NT Quoting Martin Gainty : no that would give you the count for each second interval instead of using the interval variable 5 Aveeks floor: FLOOR(X) Returns the largest integer value not greater than X. 1st (seconds/5) interval example 5/5=1 floor(5/5) = 1 supplied value would truncate and give you the int not greater than X then multiply by 5 1*5=5 is correct Aveeks sum function: SUM([DISTINCT] expr) Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used in MySQL 5.0 to sum only the distinct values of expr. SUM() returns NULL if there were no matching rows. sum(calls) from calls group by 5 * floor(seconds/5) sum(calls) from calls group by 5 * floor(5/5) sum(calls) from class group by 5 * 1 sum(calls) from class group by 5 is correct 2nd(seconds/5) interval example 10/5=2 floor(10/5)=2 supplied value would truncate and give you the int not greater than X then multiply by 5 2*5=10 is correct Aveeks sum function sum(calls) from calls group by 5 * floor(seconds/5) sum(calls) from calls group by 5 * floor(10/5) sum(calls) from class group by 5 * 2 sum(calls) from class group by 10 would be applicable only if the interval was 10 Aveek if your interval is 5 change: sum(calls) from calls group by 5 * floor(seconds/5) to sum(calls) from calls group by floor(seconds/5) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Sat, 31 Jul 2010 10:31:43 +0700 From: cuong.m...@vienthongso.com To: ave...@yahoo-inc.com CC: mustafa...@gmail.com; mysql@lists.mysql.com Subject: Re: query results group/summed by interval Hi Aveek, I think Ghulam just want to count calls for each intervals so the query should looks like this: select count(*) as total_calls, queue_seconds from calls group by queue_seconds order by total_calls; - Original Message - From: Aveek Misra To: Ghulam Mustafa , mysql@lists.mysql.com Sent: Tuesday, July 27, 2010 5:54:13 PM Subject: RE: query results group/summed by interval try this ... select 5 * floor(seconds/5) as start, 5
RE: query results group/summed by interval
no that would give you the count for each second interval instead of using the interval variable 5 Aveeks floor: FLOOR(X) Returns the largest integer value not greater than X. 1st (seconds/5) interval example 5/5=1 floor(5/5) = 1 supplied value would truncate and give you the int not greater than X then multiply by 5 1*5=5 is correct Aveeks sum function: SUM([DISTINCT] expr) Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used in MySQL 5.0 to sum only the distinct values of expr. SUM() returns NULL if there were no matching rows. sum(calls) from calls group by 5 * floor(seconds/5) sum(calls) from calls group by 5 * floor(5/5) sum(calls) from class group by 5 * 1 sum(calls) from class group by 5 is correct 2nd(seconds/5) interval example 10/5=2 floor(10/5)=2 supplied value would truncate and give you the int not greater than X then multiply by 5 2*5=10 is correct Aveeks sum function sum(calls) from calls group by 5 * floor(seconds/5) sum(calls) from calls group by 5 * floor(10/5) sum(calls) from class group by 5 * 2 sum(calls) from class group by 10 would be applicable only if the interval was 10 Aveek if your interval is 5 change: sum(calls) from calls group by 5 * floor(seconds/5) to sum(calls) from calls group by floor(seconds/5) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Sat, 31 Jul 2010 10:31:43 +0700 From: cuong.m...@vienthongso.com To: ave...@yahoo-inc.com CC: mustafa...@gmail.com; mysql@lists.mysql.com Subject: Re: query results group/summed by interval Hi Aveek, I think Ghulam just want to count calls for each intervals so the query should looks like this: select count(*) as total_calls, queue_seconds from calls group by queue_seconds order by total_calls; - Original Message - From: Aveek Misra ave...@yahoo-inc.com To: Ghulam Mustafa mustafa...@gmail.com, mysql@lists.mysql.com Sent: Tuesday, July 27, 2010 5:54:13 PM Subject: RE: query results group/summed by interval try this ... select 5 * floor(seconds/5) as start, 5 * floor(seconds/5) + 5 as end, sum(calls) from calls group by 5 * floor(seconds/5); This should give you an output of the type +---+--++ | start | end | sum(calls) | +---+--++ | 0 | 5 | 387 | | 5 | 10 | 225 | | 10 | 15 | 74 | +---+--++ Thanks Aveek From: Ghulam Mustafa [mustafa...@gmail.com] Sent: Tuesday, July 27, 2010 3:53 PM To: mysql@lists.mysql.com Subject: query results group/summed by interval Hi everyone, i have two columns (seconds, number of calls), i need to produce a report which will show total number of calls in intervals (let'say 10 seconds interval), i know i can do this programmability in my script but i was wondering if it's possible to accomplish this behavior within mysql. for example i have following data. +--+---+ | calls | queue_seconds | +--+---+ | 250 | 0.00 | | 28 | 1.00 | | 30 | 2.00 | | 56 | 3.00 | | 23 | 4.00 | | 31 | 5.00 | | 33 | 6.00 | | 50 | 7.00 | | 49 | 8.00 | | 62 | 9.00 | | 74 | 10.00 | ... ... and so on... ... +--+---+ now result should look like this with a 5 seconds interval. +--+---+ | count(*) | queue_seconds | +--+---+ | 250 | 0.00 | | 168 | 5.00 | | 268 | 10.00 | ... ... and so on... ... +--+---+ i would really appreciate your help. Best Regards. -- Ghulam Mustafa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=cuong.m...@vienthongso.com -- Best Regards, Cuongmc
RE: query results group/summed by interval
no that would give you the count for each second interval instead of using the interval variable 5 Aveeks floor: FLOOR(X) Returns the largest integer value not greater than X. 1st (seconds/5) interval example 5/5=1 floor(5/5) = 1 supplied value would truncate and give you the int not greater than X then multiply by 5 1*5=5 is correct Aveeks sum function: SUM([DISTINCT] expr) Returns the sum of expr. If the return set has no rows, SUM() returns NULL. The DISTINCT keyword can be used in MySQL 5.0 to sum only the distinct values of expr. SUM() returns NULL if there were no matching rows. sum(calls) from calls group by 5 * floor(seconds/5) sum(calls) from calls group by 5 * floor(5/5) sum(calls) from class group by 5 * 1 sum(calls) from class group by 5 is correct 2nd(seconds/5) interval example 10/5=2 floor(10/5)=2 supplied value would truncate and give you the int not greater than X then multiply by 5 2*5=10 is correct Aveeks sum function sum(calls) from calls group by 5 * floor(seconds/5) sum(calls) from calls group by 5 * floor(10/5) sum(calls) from class group by 5 * 2 sum(calls) from class group by 10 would be applicable only if the interval was 10 Aveek if your interval is 5 change: sum(calls) from calls group by 5 * floor(seconds/5) to sum(calls) from calls group by floor(seconds/5) Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. Date: Sat, 31 Jul 2010 10:31:43 +0700 From: cuong.m...@vienthongso.com To: ave...@yahoo-inc.com CC: mustafa...@gmail.com; mysql@lists.mysql.com Subject: Re: query results group/summed by interval Hi Aveek, I think Ghulam just want to count calls for each intervals so the query should looks like this: select count(*) as total_calls, queue_seconds from calls group by queue_seconds order by total_calls; - Original Message - From: Aveek Misra ave...@yahoo-inc.com To: Ghulam Mustafa mustafa...@gmail.com, mysql@lists.mysql.com Sent: Tuesday, July 27, 2010 5:54:13 PM Subject: RE: query results group/summed by interval try this ... select 5 * floor(seconds/5) as start, 5 * floor(seconds/5) + 5 as end, sum(calls) from calls group by 5 * floor(seconds/5); This should give you an output of the type +---+--++ | start | end | sum(calls) | +---+--++ | 0 | 5 | 387 | | 5 | 10 | 225 | | 10 | 15 | 74 | +---+--++ Thanks Aveek From: Ghulam Mustafa [mustafa...@gmail.com] Sent: Tuesday, July 27, 2010 3:53 PM To: mysql@lists.mysql.com Subject: query results group/summed by interval Hi everyone, i have two columns (seconds, number of calls), i need to produce a report which will show total number of calls in intervals (let'say 10 seconds interval), i know i can do this programmability in my script but i was wondering if it's possible to accomplish this behavior within mysql. for example i have following data. +--+---+ | calls | queue_seconds | +--+---+ | 250 | 0.00 | | 28 | 1.00 | | 30 | 2.00 | | 56 | 3.00 | | 23 | 4.00 | | 31 | 5.00 | | 33 | 6.00 | | 50 | 7.00 | | 49 | 8.00 | | 62 | 9.00 | | 74 | 10.00 | ... ... and so on... ... +--+---+ now result should look like this with a 5 seconds interval. +--+---+ | count(*) | queue_seconds | +--+---+ | 250 | 0.00 | | 168 | 5.00 | | 268 | 10.00 | ... ... and so on... ... +--+---+ i would really appreciate your help. Best Regards. -- Ghulam Mustafa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=cuong.m...@vienthongso.com -- Best Regards, Cuongmc
Re: query results group/summed by interval
Hi Aveek, I think Ghulam just want to count calls for each intervals so the query should looks like this: select count(*) as total_calls, queue_seconds from calls group by queue_seconds order by total_calls; - Original Message - From: Aveek Misra ave...@yahoo-inc.com To: Ghulam Mustafa mustafa...@gmail.com, mysql@lists.mysql.com Sent: Tuesday, July 27, 2010 5:54:13 PM Subject: RE: query results group/summed by interval try this ... select 5 * floor(seconds/5) as start, 5 * floor(seconds/5) + 5 as end, sum(calls) from calls group by 5 * floor(seconds/5); This should give you an output of the type +---+--++ | start | end | sum(calls) | +---+--++ | 0 |5 |387 | | 5 | 10 |225 | |10 | 15 | 74 | +---+--++ Thanks Aveek From: Ghulam Mustafa [mustafa...@gmail.com] Sent: Tuesday, July 27, 2010 3:53 PM To: mysql@lists.mysql.com Subject: query results group/summed by interval Hi everyone, i have two columns (seconds, number of calls), i need to produce a report which will show total number of calls in intervals (let'say 10 seconds interval), i know i can do this programmability in my script but i was wondering if it's possible to accomplish this behavior within mysql. for example i have following data. +--+---+ | calls | queue_seconds | +--+---+ | 250 | 0.00 | | 28 | 1.00 | | 30 | 2.00 | | 56 | 3.00 | | 23 | 4.00 | | 31 | 5.00 | | 33 | 6.00 | | 50 | 7.00 | | 49 | 8.00 | | 62 | 9.00 | | 74 | 10.00 | ... ... and so on... ... +--+---+ now result should look like this with a 5 seconds interval. +--+---+ | count(*) | queue_seconds | +--+---+ | 250 | 0.00 | | 168 | 5.00 | | 268 | 10.00 | ... ... and so on... ... +--+---+ i would really appreciate your help. Best Regards. -- Ghulam Mustafa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=cuong.m...@vienthongso.com -- Best Regards, Cuongmc. -- Nguyen Manh Cuong Phong Ky Thuat - Cong ty Vien Thong So - VTC Dien thoai: 0912051542 Gmail : philipscu...@gmail.com YahooMail : philipscu...@yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: concatenate sql query with group by and having
With some databases such as MySQL, subqueries have to be explicitly named. For example select * from (select * from (select * from table) sub1) sub2; If not, you will see an error like: ERROR 1248 (42000): Every derived table must have its own alias If I understand your problem correctly, you are looking to limit your result set to only those records that have symbols with a single unique combination of chrom, and strand. If that's correct, something like the query below might work: select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat where geneName in -- returns all geneNames (symbols) with one unique combination of chrom and strand (select geneName from -- returns all unique combinations of symbol, chrom, and strand (select distinct geneName, chrom, strand from refFlat) sub1 group by geneName having count(*) = 1) group by refSeq having count(*) = 1; Date: Wed, 28 Jul 2010 11:10:32 -0500 Subject: concatenate sql query with group by and having From: pengyu...@gmail.com To: mysql@lists.mysql.com mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A I start mysql with the above command. Then I want to select the rows from the result of the following query, provided that for any rows that have the same symbol, chrom and strand should be the same (basically, discard the rows that have the same symbols but different chrom and strand). Could anybody show me how to do it? select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat group by refSeq having count(*)=1; I think that something like SELECT name FROM (SELECT name, type_id FROM (SELECT * FROM foods)); works for sqlite3 (in terms of syntax). But the following do not work for mysql. Is this a difference between mysql and sqlite3? (I'm always confused by the difference between different variants of SQL) select * from (select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat group by refSeq having count(*)=1); -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=travis_...@hotmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
concatenate sql query with group by and having
mysql -ugenome -hgenome-mysql.cse.ucsc.edu mm9 -A I start mysql with the above command. Then I want to select the rows from the result of the following query, provided that for any rows that have the same symbol, chrom and strand should be the same (basically, discard the rows that have the same symbols but different chrom and strand). Could anybody show me how to do it? select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat group by refSeq having count(*)=1; I think that something like SELECT name FROM (SELECT name, type_id FROM (SELECT * FROM foods)); works for sqlite3 (in terms of syntax). But the following do not work for mysql. Is this a difference between mysql and sqlite3? (I'm always confused by the difference between different variants of SQL) select * from (select geneName as symbol, name as refSeq, chrom, strand, txStart from refFlat group by refSeq having count(*)=1); -- Regards, Peng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
query results group/summed by interval
Hi everyone, i have two columns (seconds, number of calls), i need to produce a report which will show total number of calls in intervals (let'say 10 seconds interval), i know i can do this programmability in my script but i was wondering if it's possible to accomplish this behavior within mysql. for example i have following data. +--+---+ | calls | queue_seconds | +--+---+ | 250 | 0.00 | | 28 | 1.00 | | 30 | 2.00 | | 56 | 3.00 | | 23 | 4.00 | | 31 | 5.00 | | 33 | 6.00 | | 50 | 7.00 | | 49 | 8.00 | | 62 | 9.00 | | 74 | 10.00 | ... ... and so on... ... +--+---+ now result should look like this with a 5 seconds interval. +--+---+ | count(*) | queue_seconds | +--+---+ | 250 | 0.00 | | 168 | 5.00 | | 268 | 10.00 | ... ... and so on... ... +--+---+ i would really appreciate your help. Best Regards. -- Ghulam Mustafa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: query results group/summed by interval
try this ... select 5 * floor(seconds/5) as start, 5 * floor(seconds/5) + 5 as end, sum(calls) from calls group by 5 * floor(seconds/5); This should give you an output of the type +---+--++ | start | end | sum(calls) | +---+--++ | 0 |5 |387 | | 5 | 10 |225 | |10 | 15 | 74 | +---+--++ Thanks Aveek From: Ghulam Mustafa [mustafa...@gmail.com] Sent: Tuesday, July 27, 2010 3:53 PM To: mysql@lists.mysql.com Subject: query results group/summed by interval Hi everyone, i have two columns (seconds, number of calls), i need to produce a report which will show total number of calls in intervals (let'say 10 seconds interval), i know i can do this programmability in my script but i was wondering if it's possible to accomplish this behavior within mysql. for example i have following data. +--+---+ | calls | queue_seconds | +--+---+ | 250 | 0.00 | | 28 | 1.00 | | 30 | 2.00 | | 56 | 3.00 | | 23 | 4.00 | | 31 | 5.00 | | 33 | 6.00 | | 50 | 7.00 | | 49 | 8.00 | | 62 | 9.00 | | 74 | 10.00 | ... ... and so on... ... +--+---+ now result should look like this with a 5 seconds interval. +--+---+ | count(*) | queue_seconds | +--+---+ | 250 | 0.00 | | 168 | 5.00 | | 268 | 10.00 | ... ... and so on... ... +--+---+ i would really appreciate your help. Best Regards. -- Ghulam Mustafa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=ave...@yahoo-inc.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Select w/ group by question
I'm having trouble formulating a query to gather the following data. I can do this via a script, but now it is more or less just bothering me if there is an easy/efficient way to gather the following data from a single query. Example Tables Products Type Cost Vendor_id -- --- apple11 apple32 apple73 pear 2 1 pear 42 pear 23 Vendors Vendor_id Vendor_name 1 Walmart 2 Target 3 Kmart I would like to obtain the least cost of each product type and its associated vendor. So...I would like to see a result similiar to the following: Type CostVendor_id Vendor_name apple 1 1 Walmart pear 2 1 Walmart (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not really concerned with which vendor is chosen in the result set here.) If I do: select a.type, min(a.cost), a.vendor_id, b.vendor_name from products a join vendors b on a.vendor_id = b.vendor_id group by a.type, a.vendor_id, b.vendor_name all rows are returned because the type/vendor_id/vendor_name are unique amongst each row. If you remove the vendor_id and vendor_name from the group by, you get a single row with the lowest cost for each product, but the vendor_id's and vendor_name's are incorrect because you are not grouping by them. Is there a way to do this from a single query. I know I can concat things together and imbed a select in my where clause to get the result I want, but this is horribly inefficient. My real tables have somewhere around 30 million rows in them. Thanks Scott
Re: Select w/ group by question
Scott, I would like to obtain the least cost of each product type and its associated vendor. See Within-group aggregates at http://www.artfulsoftware.com/infotree/queries.php. PB - On 7/14/2010 9:25 AM, Scott Mullen wrote: I'm having trouble formulating a query to gather the following data. I can do this via a script, but now it is more or less just bothering me if there is an easy/efficient way to gather the following data from a single query. Example Tables Products Type Cost Vendor_id -- --- apple11 apple32 apple73 pear 2 1 pear 42 pear 23 Vendors Vendor_id Vendor_name 1 Walmart 2 Target 3 Kmart I would like to obtain the least cost of each product type and its associated vendor. So...I would like to see a result similiar to the following: Type CostVendor_id Vendor_name apple 1 1 Walmart pear 2 1 Walmart (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not really concerned with which vendor is chosen in the result set here.) If I do: select a.type, min(a.cost), a.vendor_id, b.vendor_name from products a join vendors b on a.vendor_id = b.vendor_id group by a.type, a.vendor_id, b.vendor_name all rows are returned because the type/vendor_id/vendor_name are unique amongst each row. If you remove the vendor_id and vendor_name from the group by, you get a single row with the lowest cost for each product, but the vendor_id's and vendor_name's are incorrect because you are not grouping by them. Is there a way to do this from a single query. I know I can concat things together and imbed a select in my where clause to get the result I want, but this is horribly inefficient. My real tables have somewhere around 30 million rows in them. Thanks Scott No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.441 / Virus Database: 271.1.1/3004 - Release Date: 07/14/10 06:36:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Select w/ group by question
On Wednesday, July 14, 2010 09:25:22 am Scott Mullen wrote: I'm having trouble formulating a query to gather the following data. I can do this via a script, but now it is more or less just bothering me if there is an easy/efficient way to gather the following data from a single query. Example Tables Products Type Cost Vendor_id -- --- apple11 apple32 apple73 pear 2 1 pear 42 pear 23 Vendors Vendor_id Vendor_name 1 Walmart 2 Target 3 Kmart I would like to obtain the least cost of each product type and its associated vendor. So...I would like to see a result similiar to the following: Type CostVendor_id Vendor_name apple 1 1 Walmart pear 2 1 Walmart (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not really concerned with which vendor is chosen in the result set here.) Try this: select name, product_type, min(cost) from vendors join products on vendors.id = products.vendor_id group by product_type; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Select w/ group by question
Date: Wed, 14 Jul 2010 10:25:22 -0400 Subject: Select w/ group by question From: smulle...@gmail.com To: mysql@lists.mysql.com I'm having trouble formulating a query to gather the following data. I can do this via a script, but now it is more or less just bothering me if there is an easy/efficient way to gather the following data from a single query. Example Tables Products Type Cost Vendor_id -- --- apple 1 1 apple 3 2 apple 7 3 pear 2 1 pear 4 2 pear 2 3 Vendors Vendor_id Vendor_name 1 Walmart 2 Target 3 Kmart I would like to obtain the least cost of each product type and its associated vendor. So...I would like to see a result similiar to the following: Type Cost Vendor_id Vendor_name apple 1 1 Walmart pear 2 1 Walmart (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not really concerned with which vendor is chosen in the result set here.) If I do: select a.type, min(a.cost), a.vendor_id, b.vendor_name from products a join vendors b on a.vendor_id = b.vendor_id group by a.type,a.vendor_id, b.vendor_name all rows are returned because the type/vendor_id/vendor_name are unique amongst each row. If you remove the vendor_id and vendor_name from the group by, select a.type, min(a.cost), a.vendor_id,b.vendor_name from products a join vendors b on a.vendor_id = b.vendor_id group by a.type; you get a single row with the lowest cost for each product, but the vendor_id's and vendor_name's are incorrect because you are not grouping by them. Is there a way to do this from a single query. I know I can concat things together and imbed a select in my where clause to get the result I want, but this is horribly inefficient. My real tables have somewhere around 30 million rows in them. MGput ascending indexes on vendor_id and vendor_name columns MGreorg the tables so the rows will be in vendor_name (within vendor_id) order MGreselect MGselect a.type, min(a.cost), a.vendor_id,b.vendor_name from MGproducts a join vendors b MGon a.vendor_id = b.vendor_id MGorder by a.type; Thanks Scott _ The New Busy think 9 to 5 is a cute idea. Combine multiple calendars with Hotmail. http://www.windowslive.com/campaign/thenewbusy?tile=multicalendarocid=PID28326::T:WLMTAGL:ON:WL:en-US:WM_HMP:042010_5
Re: Select w/ group by question
Peter Thanks for the link. I've never run across this page before, but it has tons of useful informationas well as several answers on how to implement what I was trying to do. Ended up going with a solution similar to this example (from the page you referenced): SELECT item, SUBSTR( MIN( CONCAT( LPAD(price,6,0),supplier) ), 7) AS MinSupplier, LEFT( MIN( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MinPrice, SUBSTR( MAX( CONCAT( LPAD(price,6,0),supplier) ), 7) AS MaxSupplier, LEFT( MAX( CONCAT( LPAD(price,6,0),supplier) ), 6)+0 AS MaxPrice FROM products GROUP BY item; Pretty straight forward and does not require another join back to the same table with 30+ million rows. Thanks Scott On Wed, Jul 14, 2010 at 10:35 AM, Peter Brawley peter.braw...@earthlink.net wrote: Scott, I would like to obtain the least cost of each product type and its associated vendor. See Within-group aggregates at http://www.artfulsoftware.com/infotree/queries.php. PB - On 7/14/2010 9:25 AM, Scott Mullen wrote: I'm having trouble formulating a query to gather the following data. I can do this via a script, but now it is more or less just bothering me if there is an easy/efficient way to gather the following data from a single query. Example Tables Products Type Cost Vendor_id -- --- apple11 apple32 apple73 pear 2 1 pear 42 pear 23 Vendors Vendor_id Vendor_name 1 Walmart 2 Target 3 Kmart I would like to obtain the least cost of each product type and its associated vendor. So...I would like to see a result similiar to the following: Type CostVendor_id Vendor_name apple 1 1 Walmart pear 2 1 Walmart (Note: both vendors 1 and 3 have the same cost which is the lowest. I'm not really concerned with which vendor is chosen in the result set here.) If I do: select a.type, min(a.cost), a.vendor_id, b.vendor_name from products a join vendors b on a.vendor_id = b.vendor_id group by a.type, a.vendor_id, b.vendor_name all rows are returned because the type/vendor_id/vendor_name are unique amongst each row. If you remove the vendor_id and vendor_name from the group by, you get a single row with the lowest cost for each product, but the vendor_id's and vendor_name's are incorrect because you are not grouping by them. Is there a way to do this from a single query. I know I can concat things together and imbed a select in my where clause to get the result I want, but this is horribly inefficient. My real tables have somewhere around 30 million rows in them. Thanks Scott No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.441 / Virus Database: 271.1.1/3004 - Release Date: 07/14/10 06:36:00
Re: Duplicate entries despite group by
Is the CREATE TABLE you show the result of SHOW CREATE TABLE or your own
create statement? If the latter, please show the output of SHOW CREATE.
Does SELECT succeed if you remove the INSERT part of the statement?
You might want to consider adding an index on transactionlogid, this
could bring down query time significantly.
/ Carsten
Yang Zhang skrev:
I have the following table:
CREATE TABLE `graph` (
`tableid1` varchar(20) NOT NULL,
`tupleid1` int(11) NOT NULL,
`tableid2` varchar(20) NOT NULL,
`tupleid2` int(11) NOT NULL,
`node1` int(11) NOT NULL,
`node2` int(11) NOT NULL,
`weight` int(10) NOT NULL,
PRIMARY KEY (`tableid1`,`tupleid1`,`tableid2`,`tupleid2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
and I'm running this query (note the 'group by'):
insert into graph (node1, node2, tableid1, tupleid1, tableid2,
tupleid2, weight)
select 0, 0, a.tableid, a.tupleid, b.tableid, b.tupleid, count(*)
from transactionlog a, transactionlog b
where a.transactionid = b.transactionid
and (a.tableid, a.tupleid) {''} (b.tableid, b.tupleid)
group by a.tableid, a.tupleid, b.tableid, b.tupleid
However, after running for a few hours, the query fails with the
following error:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
Duplicate entry 'new_order-248642-order_line-13126643' for key
'group_key'
How is this possible? There were no concurrently running queries
inserting into 'graph'. I'm using mysql-5.4.3; is this a beta
bug/anyone else happen to know something about this? Thanks in
advance.
--
Yang Zhang
http://www.mit.edu/~y_z/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Duplicate entries despite group by
I have the following table:
CREATE TABLE `graph` (
`tableid1` varchar(20) NOT NULL,
`tupleid1` int(11) NOT NULL,
`tableid2` varchar(20) NOT NULL,
`tupleid2` int(11) NOT NULL,
`node1` int(11) NOT NULL,
`node2` int(11) NOT NULL,
`weight` int(10) NOT NULL,
PRIMARY KEY (`tableid1`,`tupleid1`,`tableid2`,`tupleid2`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
and I'm running this query (note the 'group by'):
insert into graph (node1, node2, tableid1, tupleid1, tableid2,
tupleid2, weight)
select 0, 0, a.tableid, a.tupleid, b.tableid, b.tupleid, count(*)
from transactionlog a, transactionlog b
where a.transactionid = b.transactionid
and (a.tableid, a.tupleid) {''} (b.tableid, b.tupleid)
group by a.tableid, a.tupleid, b.tableid, b.tupleid
However, after running for a few hours, the query fails with the
following error:
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException:
Duplicate entry 'new_order-248642-order_line-13126643' for key
'group_key'
How is this possible? There were no concurrently running queries
inserting into 'graph'. I'm using mysql-5.4.3; is this a beta
bug/anyone else happen to know something about this? Thanks in
advance.
--
Yang Zhang
http://www.mit.edu/~y_z/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Consult with group
i have a question, i'm searching for a while and do not find some reference how to do that. a simple query return this: dato1 dato2 dato3 estado1 fecha1 dato1 dato2 dato3 estado2 fecha2 dato1 dato2 dato3 estado3 fecha3 dato4 dato5 dato6 estado1 fecha4 dato4 dato5 dato6 estado2 fecha5 dato4 dato5 dato6 estado3 fecha6 and i need this: dato1 dato2 dato3 fecha1 fecha2 fecha3 dato4 dato5 dato6 fecha4 fecha5 fecha6 any help?, and i'm sorry por my english. thanks! -- ing. paredes aguilar, armando http://www.sinapsisperu.com/ Desarrollador
Re: Consult with group
In the last episode (Nov 28), armando said: i have a question, i'm searching for a while and do not find some reference how to do that. a simple query return this: dato1 dato2 dato3 estado1 fecha1 dato1 dato2 dato3 estado2 fecha2 dato1 dato2 dato3 estado3 fecha3 dato4 dato5 dato6 estado1 fecha4 dato4 dato5 dato6 estado2 fecha5 dato4 dato5 dato6 estado3 fecha6 and i need this: dato1 dato2 dato3 fecha1 fecha2 fecha3 dato4 dato5 dato6 fecha4 fecha5 fecha6 any help?, and i'm sorry por my english. The group_concat() function might help you here. It will return fecha1,fecha2,fecha3, which you can then split in your application code. Make sure you don't include your the estado or fecha columns in the GROUP BY clause (in your case you would want to group by the first three columns only). http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html#function_group-concat -- Dan Nelson dnel...@allantgroup.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
help with group by
I've written a helpdesk ticket problem and am working on the statistics module. I'm having problems with group by. For instance, I want to get the count of the number of different problem types, by how many were solved by each person. This is my statement: mysql select distinct accepted_by, problem_type, count(*) from form where ((problem_type is not NULL) (problem_type != 'Test') (accepted_by is not null)) group by problem_type; +-+-+--+ | accepted_by | problem_type| count(*) | +-+-+--+ | awilliam| Computer Hardware | 13 | | awilliam| Computer Peripheral | 16 | | awilliam| Computer Software | 138 | | awilliam| Delete User |4 | | smccoy | Networking | 17 | | awilliam| New User|6 | | jomiles | Printer | 21 | | awilliam| Server | 47 | | sokolsky| Telephone |6 | +-+-+--+ 9 rows in set (0.00 sec) But it is leaving out two of the support staff, and smccoy and jomiles have also solved Computer Software problems, but it's only showing awilliam as solving Computer Software problems. I think its just showing accepted_by's values by first occurrence of accepted_by on problem_type. Here's the two users its not even showing: mysql select accepted_by, problem_type, count(*) from form where (accepted_by = 'ehynum') group by problem_type; +-+-+--+ | accepted_by | problem_type| count(*) | +-+-+--+ | ehynum | Computer Peripheral |1 | | ehynum | Computer Software |5 | | ehynum | Telephone |1 | +-+-+--+ 3 rows in set (0.00 sec) mysql select accepted_by, problem_type, count(*) from form where (accepted_by = 'dbrooks') group by problem_type; +-+-+--+ | accepted_by | problem_type| count(*) | +-+-+--+ | dbrooks | Computer Peripheral |2 | | dbrooks | Computer Software |9 | | dbrooks | Networking |2 | | dbrooks | Printer |3 | | dbrooks | Server |3 | +-+-+--+ 5 rows in set (0.01 sec) but what I really need is an SQL statement that would return this, but I'm at a loss as to what that would be: +-+-+--+ | accepted_by | problem_type| count(*) | +-+-+--+ | awilliam| Computer Hardware |6 | | awilliam| Computer Peripheral |7 | | awilliam| Computer Software | 64 | | awilliam| Delete User |4 | | awilliam| Networking | 10 | | awilliam| New User|5 | | awilliam| Printer |4 | | awilliam| Server | 33 | | awilliam| Telephone |1 | | awilliam| Test|1 | | dbrooks | Computer Peripheral |2 | | dbrooks | Computer Software |9 | | dbrooks | Networking |2 | | dbrooks | Printer |3 | | dbrooks | Server |3 | | ehynum | Computer Peripheral |1 | | ehynum | Computer Software |5 | | ehynum | Telephone |1 | | jomiles | Computer Hardware |5 | | jomiles | Computer Peripheral |6 | | jomiles | Computer Software | 44 | | jomiles | Networking |1 | | jomiles | Printer | 12 | | jomiles | Server |7 | | smccoy | Computer Hardware |2 | | smccoy | Computer Software | 15 | | smccoy | Networking |4 | | smccoy | New User|1 | | smccoy | Printer |2 | | smccoy | Server |4 | | sokolsky| Computer Software |1 | | sokolsky| Telephone |4 | +-+-+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: help with group by
try this: select accepted_by, problem_type, count(*) from form where problem_type is not NULL AND problem_type != 'Test' AND accepted_by is not null group by accepted_by, problem_type On Wed, Oct 28, 2009 at 12:05 PM, Adam Williams awill...@mdah.state.ms.us wrote: I've written a helpdesk ticket problem and am working on the statistics module. I'm having problems with group by. For instance, I want to get the count of the number of different problem types, by how many were solved by each person. This is my statement: mysql select distinct accepted_by, problem_type, count(*) from form where ((problem_type is not NULL) (problem_type != 'Test') (accepted_by is not null)) group by problem_type; +-+-+--+ | accepted_by | problem_type | count(*) | +-+-+--+ | awilliam | Computer Hardware | 13 | | awilliam | Computer Peripheral | 16 | | awilliam | Computer Software | 138 | | awilliam | Delete User | 4 | | smccoy | Networking | 17 | | awilliam | New User | 6 | | jomiles | Printer | 21 | | awilliam | Server | 47 | | sokolsky | Telephone | 6 | +-+-+--+ 9 rows in set (0.00 sec) But it is leaving out two of the support staff, and smccoy and jomiles have also solved Computer Software problems, but it's only showing awilliam as solving Computer Software problems. I think its just showing accepted_by's values by first occurrence of accepted_by on problem_type. Here's the two users its not even showing: mysql select accepted_by, problem_type, count(*) from form where (accepted_by = 'ehynum') group by problem_type; +-+-+--+ | accepted_by | problem_type | count(*) | +-+-+--+ | ehynum | Computer Peripheral | 1 | | ehynum | Computer Software | 5 | | ehynum | Telephone | 1 | +-+-+--+ 3 rows in set (0.00 sec) mysql select accepted_by, problem_type, count(*) from form where (accepted_by = 'dbrooks') group by problem_type; +-+-+--+ | accepted_by | problem_type | count(*) | +-+-+--+ | dbrooks | Computer Peripheral | 2 | | dbrooks | Computer Software | 9 | | dbrooks | Networking | 2 | | dbrooks | Printer | 3 | | dbrooks | Server | 3 | +-+-+--+ 5 rows in set (0.01 sec) but what I really need is an SQL statement that would return this, but I'm at a loss as to what that would be: +-+-+--+ | accepted_by | problem_type | count(*) | +-+-+--+ | awilliam | Computer Hardware | 6 | | awilliam | Computer Peripheral | 7 | | awilliam | Computer Software | 64 | | awilliam | Delete User | 4 | | awilliam | Networking | 10 | | awilliam | New User | 5 | | awilliam | Printer | 4 | | awilliam | Server | 33 | | awilliam | Telephone | 1 | | awilliam | Test | 1 | | dbrooks | Computer Peripheral | 2 | | dbrooks | Computer Software | 9 | | dbrooks | Networking | 2 | | dbrooks | Printer | 3 | | dbrooks | Server | 3 | | ehynum | Computer Peripheral | 1 | | ehynum | Computer Software | 5 | | ehynum | Telephone | 1 | | jomiles | Computer Hardware | 5 | | jomiles | Computer Peripheral | 6 | | jomiles | Computer Software | 44 | | jomiles | Networking | 1 | | jomiles | Printer | 12 | | jomiles | Server | 7 | | smccoy | Computer Hardware | 2 | | smccoy | Computer Software | 15 | | smccoy | Networking | 4 | | smccoy | New User | 1 | | smccoy | Printer | 2 | | smccoy | Server | 4 | | sokolsky | Computer Software | 1 | | sokolsky | Telephone | 4 | +-+-+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift
Re: help with group by
works perfectly, i didn't know you could use multiple columns in the group by. thanks a bunch! Michael Dykman wrote: try this: select accepted_by, problem_type, count(*) from form where problem_type is not NULL AND problem_type != 'Test' AND accepted_by is not null group by accepted_by, problem_type -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Performance tuning a group by with percentage
Running MySql 5.0.85, I need to be as efficient as possible about a few queries. If I could get a little review, I would appreciate it. I collect data in the millions, and need the top 50 grouped by one field, with a percentage of how much those top 50 occupy. Here is what I have come up with... 1) I have a feeling I can be more efficient, perhaps with a join 2) How can I get the percentage to be of precision in the hundredths, so * 100.00 ie: .07 becomes 7.00, getting SQL errors if I (percentage * 100) SELECT user_agent_parsed, user_agent_original, COUNT( user_agent_parsed ) AS thecount, COUNT( * ) / ( SELECT COUNT( * ) FROM agents ) AS percentage FROM agents GROUP BY user_agent_parsed ORDER BY thecount DESC LIMIT 50; Second issue, once a day I need to archive the result of the above. Any suggestions on how to best to do that? I can schedule with cron, or in my case, launchd, unless someone has a better suggestion. Would you think that a simple 'SELECT (the above) INTO foo' would suffice? ( I will add a date stamp as well ) Thanks all. -- Scott * If you contact me off list replace talklists@ with scott@ * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Stupid GROUP BY question
It must be too late in the week… Suppose table Cust has one field, CustID. Suppose table Notes has four fields: NoteID (unique), CustID, NoteTime, and NoteText. A customer can have zero or more notes. Now here’s the seemingly simple problem that I’m trying to solve: I want to find the newest note (if any) for each customer. If all I want is the date, then I can do SELECT Cust.CustID, MAX(Notes.NoteTime) FROM Cust LEFT JOIN Notes ON Cust.CustID = Notes.Cust_ID GROUP BY Cust.CustID; That will work just fine, but now I also want the NoteText associated with the newest note. Obviously I can’t use MAX(NoteText). I could do this using a temporary table, but it seems like there should be another way. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com
Re: Stupid GROUP BY question
Jerry, I want to find the newest note (if any) for each customer. See Within-group aggregates at http://www.artfulsoftware.com/infotree/queries.php http://www.artfulsoftware.com/queries.php PB - Jerry Schwartz wrote: It must be too late in the week... Suppose table Cust has one field, CustID. Suppose table Notes has four fields: NoteID (unique), CustID, NoteTime, and NoteText. A customer can have zero or more notes. Now here's the seemingly simple problem that I'm trying to solve: I want to find the newest note (if any) for each customer. If all I want is the date, then I can do SELECT Cust.CustID, MAX(Notes.NoteTime) FROM Cust LEFT JOIN Notes ON Cust.CustID = Notes.Cust_ID GROUP BY Cust.CustID; That will work just fine, but now I also want the NoteText associated with the newest note. Obviously I can't use MAX(NoteText). I could do this using a temporary table, but it seems like there should be another way. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.409 / Virus Database: 270.13.112/2394 - Release Date: 09/25/09 05:51:00
RE: Stupid GROUP BY question
Commonly refered to as a groupwise max http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html http://jan.kneschke.de/projects/mysql/groupwise-max/ Regards, Gavin Towey -Original Message- From: Jerry Schwartz [mailto:jschwa...@the-infoshop.com] Sent: Friday, September 25, 2009 1:28 PM To: mysql@lists.mysql.com Subject: Stupid GROUP BY question It must be too late in the week… Suppose table Cust has one field, CustID. Suppose table Notes has four fields: NoteID (unique), CustID, NoteTime, and NoteText. A customer can have zero or more notes. Now here’s the seemingly simple problem that I’m trying to solve: I want to find the newest note (if any) for each customer. If all I want is the date, then I can do SELECT Cust.CustID, MAX(Notes.NoteTime) FROM Cust LEFT JOIN Notes ON Cust.CustID = Notes.Cust_ID GROUP BY Cust.CustID; That will work just fine, but now I also want the NoteText associated with the newest note. Obviously I can’t use MAX(NoteText). I could do this using a temporary table, but it seems like there should be another way. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 http://www.the-infoshop.com www.the-infoshop.com The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Group by optimization
Having problems with this query, any ideas on how to optimize this further? mysql explain SELECT cache_property_str.name as name, SUM(cache_property_str.tally) as count FROM cache_property_str WHERE cache_property_str.status = 1 AND cache_property_str.event_id IN (84007,84862,84965,85356,85453,85659,85874,86049,86319,86451,86571,86740,86800,86966,87138,87233,87720,88015,88179,88359,88517,88694,88805,89026,89164,89277,89396,89698,90002,90384,90428,91561,92128,92339,92743,93006,93227,93645,93755,93844,93966,94120,94330,94487,94712,95068,95301,95439,95677,95822,96138,97151,97362,97512,97771,97986,98419,98642,99033,99291,99601,99835,100529,100695,100883,101070,101976,102435,102705,102864,103098,103303,103415,103612,103799,103841,104422,104792,105027,105218,105526,105689,105909,106173,106311,106459,107118,107320,107662,107970,108155,108379,108418,108618,108779,108960,109506,109691,110067,110469,110698,110806,111201,111286,111641,112174,112375,112568,112656,113094,113248,113344,113449,113561,113909,114170,114322,114432,115059,115146,115244,115541,115689,116305,116405,116762,117148,117296,117389,117504,117779,117945,118285,118447,118571,118752) GROUP BY cache_property_str.name ORDER BY NULL LIMIT 10; ++-++---+-+-+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+-+-+-+--+--+--+ | 1 | SIMPLE | cache_property_str | range | property_unique_idx | property_unique_idx | 5 | NULL | 245 | Using where; Using temporary | ++-++---+-+-+-+--+--+--+ 1 row in set (0.00 sec) CREATE TABLE `cache_property_str` ( `id` int(11) unsigned NOT NULL auto_increment, `event_id` int(11) unsigned NOT NULL, `name` binary(16) NOT NULL, `value` binary(16) NOT NULL, `tally` bigint(20) unsigned NOT NULL, `status` tinyint(2) unsigned NOT NULL, `modified` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `property_unique_idx` (`event_id`,`status`,`name`,`value`) ) ENGINE=InnoDB AUTO_INCREMENT=4041064 DEFAULT CHARSET=latin1
Re: Group by optimization
Suhail, Having problems with this query, any ideas on how to optimize this further? Did you try writing the event_ids in your IN() list to a temp table joining to that table? PB - Suhail Doshi wrote: Having problems with this query, any ideas on how to optimize this further? mysql explain SELECT cache_property_str.name as name, SUM(cache_property_str.tally) as count FROM cache_property_str WHERE cache_property_str.status = 1 AND cache_property_str.event_id IN (84007,84862,84965,85356,85453,85659,85874,86049,86319,86451,86571,86740,86800,86966,87138,87233,87720,88015,88179,88359,88517,88694,88805,89026,89164,89277,89396,89698,90002,90384,90428,91561,92128,92339,92743,93006,93227,93645,93755,93844,93966,94120,94330,94487,94712,95068,95301,95439,95677,95822,96138,97151,97362,97512,97771,97986,98419,98642,99033,99291,99601,99835,100529,100695,100883,101070,101976,102435,102705,102864,103098,103303,103415,103612,103799,103841,104422,104792,105027,105218,105526,105689,105909,106173,106311,106459,107118,107320,107662,107970,108155,108379,108418,108618,108779,108960,109506,109691,110067,110469,110698,110806,111201,111286,111641,112174,112375,112568,112656,113094,113248,113344,113449,113561,113909,114170,114322,114432,115059,115146,115244,115541,115689,116305,116405,116762,117148,117296,117389,117504,117779,117945,118285,118447,118571,118752) GROUP BY cache_property_str.name ORDER BY NULL LIMIT 10; ++-++---+-+-+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+-+-+-+--+--+--+ | 1 | SIMPLE | cache_property_str | range | property_unique_idx | property_unique_idx | 5 | NULL | 245 | Using where; Using temporary | ++-++---+-+-+-+--+--+--+ 1 row in set (0.00 sec) CREATE TABLE `cache_property_str` ( `id` int(11) unsigned NOT NULL auto_increment, `event_id` int(11) unsigned NOT NULL, `name` binary(16) NOT NULL, `value` binary(16) NOT NULL, `tally` bigint(20) unsigned NOT NULL, `status` tinyint(2) unsigned NOT NULL, `modified` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `property_unique_idx` (`event_id`,`status`,`name`,`value`) ) ENGINE=InnoDB AUTO_INCREMENT=4041064 DEFAULT CHARSET=latin1 No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.392 / Virus Database: 270.13.58/2306 - Release Date: 08/16/09 06:09:00
Re: Group by optimization
Peter, I am fairly certain, it's not slow because of the event_id look up but because of the GROUP BY Suhail On Sun, Aug 16, 2009 at 2:56 PM, Peter Brawley peter.braw...@earthlink.netwrote: Suhail, Having problems with this query, any ideas on how to optimize this further? Did you try writing the event_ids in your IN() list to a temp table joining to that table? PB - Suhail Doshi wrote: Having problems with this query, any ideas on how to optimize this further? mysql explain SELECT cache_property_str.name as name, SUM(cache_property_str.tally) as count FROM cache_property_str WHERE cache_property_str.status = 1 AND cache_property_str.event_id IN (84007,84862,84965,85356,85453,85659,85874,86049,86319,86451,86571,86740,86800,86966,87138,87233,87720,88015,88179,88359,88517,88694,88805,89026,89164,89277,89396,89698,90002,90384,90428,91561,92128,92339,92743,93006,93227,93645,93755,93844,93966,94120,94330,94487,94712,95068,95301,95439,95677,95822,96138,97151,97362,97512,97771,97986,98419,98642,99033,99291,99601,99835,100529,100695,100883,101070,101976,102435,102705,102864,103098,103303,103415,103612,103799,103841,104422,104792,105027,105218,105526,105689,105909,106173,106311,106459,107118,107320,107662,107970,108155,108379,108418,108618,108779,108960,109506,109691,110067,110469,110698,110806,111201,111286,111641,112174,112375,112568,112656,113094,113248,113344,113449,113561,113909,114170,114322,114432,115059,115146,115244,115541,115689,116305,116405,116762,117148,117296,117389,117504,117779,117945,118285,118447,118571,118752) GROUP BY cache_property_str.name ORDER BY NULL LIMIT 10; ++-++---+-+-+-+--+--+--+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+-+-+-+--+--+--+ | 1 | SIMPLE | cache_property_str | range | property_unique_idx | property_unique_idx | 5 | NULL | 245 | Using where; Using temporary | ++-++---+-+-+-+--+--+--+ 1 row in set (0.00 sec) CREATE TABLE `cache_property_str` ( `id` int(11) unsigned NOT NULL auto_increment, `event_id` int(11) unsigned NOT NULL, `name` binary(16) NOT NULL, `value` binary(16) NOT NULL, `tally` bigint(20) unsigned NOT NULL, `status` tinyint(2) unsigned NOT NULL, `modified` datetime NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `property_unique_idx` (`event_id`,`status`,`name`,`value`) ) ENGINE=InnoDB AUTO_INCREMENT=4041064 DEFAULT CHARSET=latin1 -- No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.392 / Virus Database: 270.13.58/2306 - Release Date: 08/16/09 06:09:00
Re: Hard? query to with group order by group head's name
Hi Darryle, Your result was: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 2 | Ann | 1 | | 3 | David | NULL | | 1 | Elim | NULL | | 5 | Jane | 3 | | 4 | John | 3 | +---+---+-+ which not groups correctly. Seems it's a hard query. - Original Message - From: Darryle Steplight dstepli...@gmail.com To: Elim PDT e...@pdtnetworks.net Cc: mysql@lists.mysql.com Sent: Wednesday, July 15, 2009 11:50 PM Subject: Re: Hard? query to with group order by group head's name Hi Elim, I didn't test it out but it sounds like you want to do this SELECT * FROM group_members GROUP BY head_id, member_id ORDER BY name ASC . On Thu, Jul 16, 2009 at 1:20 AM, Elim PDTe...@pdtnetworks.net wrote: My table group_member looks like this: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 1 | Elim | NULL | | 2 | Ann | 1 | | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | +---+---+-+ Record with null head_id means the member is a group head. Record with head_id k are in the group with head whoes id equals k. I like to fetch the rows in the following ordaer | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim | NULL | | 2 | Ann | 1 | That is (1) A head-row follewed by the group members with that head (2)head rows are ordered alphabetically by name. What the query looks like? Thanks -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Hard? query to with group order by group head's name
On Thu, Jul 16, 2009 at 1:20 AM, Elim PDTe...@pdtnetworks.net wrote: My table group_member looks like this: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 1 | Elim | NULL | | 2 | Ann | 1 | | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | +---+---+-+ Record with null head_id means the member is a group head. Record with head_id k are in the group with head whoes id equals k. I like to fetch the rows in the following ordaer | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim | NULL | | 2 | Ann | 1 | That is (1) A head-row follewed by the group members with that head (2)head rows are ordered alphabetically by name. What the query looks like? Thanks You need to create your own sort values, and link to the head name. So really you are sorting on head name + head_id. Since sometimes the head name is the current record, sometimes it's a parent record, you need to conditional check which type of record it is and built the sort value. SELECT tablename.*, IF(tablename.head_id=NULL, CONCAT(tablename.name, tablename.member_id), CONCAT(heads.name, tablename.head_id) ) AS SortValue FROM tablename LEFT JOIN tablename AS heads ON tablename.head_id=heads.member_id ORDER BY SortValue Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Hard? query to with group order by group head's name
On Thu, Jul 16, 2009 at 1:20 AM, Elim PDTe...@pdtnetworks.net wrote: My table group_member looks like this: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 1 | Elim | NULL | | 2 | Ann | 1 | | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | +---+---+-+ Record with null head_id means the member is a group head. Record with head_id k are in the group with head whoes id equals k. I like to fetch the rows in the following ordaer | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim | NULL | | 2 | Ann | 1 | That is (1) A head-row follewed by the group members with that head (2)head rows are ordered alphabetically by name. What the query looks like? Thanks I hope this is not a school assignment. What I came up with was to create a new order column that I populated with the name of the HEAD. Then I can order by the head, head_id, and the member_id mysql select t1.member_id, t1.name, t1.head_id from ( select m1.*, IF ( m2.name IS NULL, m1.name, m2.name) as groupName from group_member as m1 left outer join group_member as m2 ON ( m1.head_id = m2.member_id ) order by groupName, m1.head_id, m1.member_id ) AS t1; +---+---+-+ | member_id | name | head_id | +---+---+-+ | 3 | David | NULL| | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim | NULL| | 2 | Ann | 1 | +---+---+-+ 5 rows in set (0.01 sec) It seemed to work without the order by member_id but I'll assume that is a fact of the small sample size. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Group by column and Sum another
I am hoping I can get some help with a query I am trying to construct: I want to group by a 'contract' column and get the sum of the 'amlp' column values associated with each contract. I can do a select and group by on contract select contract from maintenance group by contract; and I will get each unique contract. However, I want to sum the amlp values per unique contract and I am not sure how to construct that select statement. I tried: select contract , sum(amlp) from maintenance where contract=(select contract from maintenance group by contract); But that resulted in a ERROR 1242 (21000): Subquery returns more than 1 row error. Any suggestions? Hagen Finley Boulder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Group by column and Sum another
select contract , sum(amlp) from maintenance group by contract; On Mon, Jul 20, 2009 at 6:50 PM, Hagen finha...@comcast.net wrote: I am hoping I can get some help with a query I am trying to construct: I want to group by a 'contract' column and get the sum of the 'amlp' column values associated with each contract. I can do a select and group by on contract select contract from maintenance group by contract; and I will get each unique contract. However, I want to sum the amlp values per unique contract and I am not sure how to construct that select statement. I tried: select contract , sum(amlp) from maintenance where contract=(select contract from maintenance group by contract); But that resulted in a ERROR 1242 (21000): Subquery returns more than 1 row error. Any suggestions? Hagen Finley Boulder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=omel...@gmail.com -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/
RE: Group by column and Sum another
Thanks! That did the trick. -Original Message- From: Olexandr Melnyk [mailto:omel...@gmail.com] Sent: Monday, July 20, 2009 10:02 AM To: mysql@lists.mysql.com Subject: Re: Group by column and Sum another select contract , sum(amlp) from maintenance group by contract; On Mon, Jul 20, 2009 at 6:50 PM, Hagen finha...@comcast.net wrote: I am hoping I can get some help with a query I am trying to construct: I want to group by a 'contract' column and get the sum of the 'amlp' column values associated with each contract. I can do a select and group by on contract select contract from maintenance group by contract; and I will get each unique contract. However, I want to sum the amlp values per unique contract and I am not sure how to construct that select statement. I tried: select contract , sum(amlp) from maintenance where contract=(select contract from maintenance group by contract); But that resulted in a ERROR 1242 (21000): Subquery returns more than 1 row error. Any suggestions? Hagen Finley Boulder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=omel...@gmail.com -- Sincerely yours, Olexandr Melnyk http://omelnyk.net/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
what outputs/illustrates a table's structure? - WAS: Re: Hard? query to with group order by group head's name
My table group_member looks like this: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 1 | Elim |NULL | | 2 | Ann | 1 | | 3 | David |NULL | | 4 | John | 3 | | 5 | Jane | 3 | +---+---+-+ I see such nice formated text output serving to illustrate people's tables and I think it must be due to some code which is spitting that out, rather than people typing so painstakingly. What is that function/MySQL/code? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: what outputs/illustrates a table's structure? - WAS: Re: Hard? query to with group order by group head's name
I see such nice formated text output serving to illustrate people's tables and I think it must be due to some code which is spitting that out, rather than people typing so painstakingly. What is that function/MySQL/code? It's the default output format of the mysql command line client, nothing special. Marcus -- Meaning that on a shared hosting situation, without ssh, then I cannot do that, right? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: what outputs/illustrates a table's structure? - WAS: Re: Hard? query to with group order by group head's name
On 16 Jul 2009, at 15:02, Govinda wrote: I see such nice formated text output serving to illustrate people's tables and I think it must be due to some code which is spitting that out, rather than people typing so painstakingly. What is that function/MySQL/code? It's the default output format of the mysql command line client, nothing special. Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of i...@hand CRM solutions mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: what outputs/illustrates a table's structure? - WAS: Re: Hard? query to with group order by group head's name
On 16 Jul 2009, at 15:26, Govinda wrote: Meaning that on a shared hosting situation, without ssh, then I cannot do that, right? Not necessarily - you can run the client locally and connect to the remote DB. It depends if your host allows remote access to mysql (they might do on request). Marcus -- Marcus Bointon Synchromedia Limited: Creators of http://www.smartmessages.net/ UK resellers of i...@hand CRM solutions mar...@synchromedia.co.uk | http://www.synchromedia.co.uk/ smime.p7s Description: S/MIME cryptographic signature
Hard? query to with group order by group head's name
My table group_member looks like this: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 1 | Elim |NULL | | 2 | Ann | 1 | | 3 | David |NULL | | 4 | John | 3 | | 5 | Jane | 3 | +---+---+-+ Record with null head_id means the member is a group head. Record with head_id k are in the group with head whoes id equals k. I like to fetch the rows in the following ordaer | 3 | David |NULL | | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim |NULL | | 2 | Ann | 1 | That is (1) A head-row follewed by the group members with that head (2)head rows are ordered alphabetically by name. What the query looks like? Thanks
Re: Hard? query to with group order by group head's name
Hi Elim, I didn't test it out but it sounds like you want to do this SELECT * FROM group_members GROUP BY head_id, member_id ORDER BY name ASC . On Thu, Jul 16, 2009 at 1:20 AM, Elim PDTe...@pdtnetworks.net wrote: My table group_member looks like this: +---+---+-+ | member_id | name | head_id | +---+---+-+ | 1 | Elim | NULL | | 2 | Ann | 1 | | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | +---+---+-+ Record with null head_id means the member is a group head. Record with head_id k are in the group with head whoes id equals k. I like to fetch the rows in the following ordaer | 3 | David | NULL | | 4 | John | 3 | | 5 | Jane | 3 | | 1 | Elim | NULL | | 2 | Ann | 1 | That is (1) A head-row follewed by the group members with that head (2)head rows are ordered alphabetically by name. What the query looks like? Thanks -- A: It reverses the normal flow of conversation. Q: What's wrong with top-posting? A: Top-posting. Q: What's the biggest scourge on plain text email discussions? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Using RANDOM before GROUP BY technique returned only a single column
Hi, I am using a technique described here at: http://forums.mysql.com/read.php?20,227102,227102#msg-227102 To make results Random before applying the GROUP BY method. The query to my table structure is this: SELECT r.physicians_id, (SELECT r1.id FROM physicians_images r1 WHERE (r.physicians_id=r1.physicians_id AND procedures LIKE '%8%' AND category = 'beforeafter' AND publish = 1) ORDER BY rand() LIMIT 1) AS 'id' FROM physicians_images r GROUP BY r.physicians_id ; I have the following columns: id, physicians_id, imageName, imagePath, title, imageDetails, DateTime, publish, Date, ip, category, site, patientName and procedures The query is working well, but it only returned the ID, not the whole row. How can i get the complete rows in results? I tried using * but its returning with the following error: #1241 - Operand should contain 1 column(s) Any help?? Thanks! --- http://www.visualbooks.com.pk/
a possible group issue???
Hi...
I have the following...
mysql INSERT INTO ParseScriptTBL VALUES
- ('auburnCourse.py',40,1,1),
- ('auburnFaculty.py',40,2,2),
- ('uofl.py',2,1,3),
- ('uky.py',3,1,4),
- ('ufl.py',4,1,5)
- ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql select * from ParseScriptTBL as p join universityTBL as u on
u.ID=p.CollegeID where u.ID=40;
+--+---+--+++
| ScriptName | CollegeID | pTypeID | ScriptID | ID |
+--+---+--+++
| auburnCourse.py | 40 | 1 | 1 | 40 |
| auburnFaculty.py | 40 | 2 | 2 | 40 |
+--+---+--+++
2 rows in set (0.00 sec)
i'd like to have a query that gives me both scripts for the college in the
same row...
keeping in mind that some colleges will have no scripts, some will have only
one, and some will have both...
i've tried to do the query, and added a group by CollegeID' with no luck..
so how can i combine the two rows to get a single row??
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: a possible group issue???
Try with GROUP_CONCAT(ScriptName)
http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
2009/6/12 bruce bedoug...@earthlink.net
Hi...
I have the following...
mysql INSERT INTO ParseScriptTBL VALUES
- ('auburnCourse.py',40,1,1),
- ('auburnFaculty.py',40,2,2),
- ('uofl.py',2,1,3),
- ('uky.py',3,1,4),
- ('ufl.py',4,1,5)
- ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql select * from ParseScriptTBL as p join universityTBL as u on
u.ID=p.CollegeID where u.ID=40;
+--+---+--+++
| ScriptName | CollegeID | pTypeID | ScriptID | ID |
+--+---+--+++
| auburnCourse.py | 40 | 1 | 1 | 40 |
| auburnFaculty.py | 40 | 2 | 2 | 40 |
+--+---+--+++
2 rows in set (0.00 sec)
i'd like to have a query that gives me both scripts for the college in the
same row...
keeping in mind that some colleges will have no scripts, some will have
only
one, and some will have both...
i've tried to do the query, and added a group by CollegeID' with no luck..
so how can i combine the two rows to get a single row??
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=maxb...@gmail.com
RE: a possible group issue???
hi martin...
thanks for the reply.. but that still generates two separate rows as well...
-Original Message-
From: Martin Gainty [mailto:mgai...@hotmail.com]
Sent: Friday, June 12, 2009 12:04 PM
To: bruce Douglas
Subject: RE: a possible group issue???
mysql select * from ParseScriptTBL as p join universityTBL as u on
u.ID=p.CollegeID where u.ID=40
GROUP BY CollegeID WITH ROLLUP
;
http://dev.mysql.com/doc/refman/6.0/en/group-by-modifiers.html
Martin Gainty
__
Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene
Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte
Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht
dient lediglich dem Austausch von Informationen und entfaltet keine
rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von
E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen.
Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le
destinataire prévu, nous te demandons avec bonté que pour satisfaire
informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie
de ceci est interdite. Ce message sert à l'information seulement et n'aura
pas n'importe quel effet légalement obligatoire. Étant donné que les email
peuvent facilement être sujets à la manipulation, nous ne pouvons accepter
aucune responsabilité pour le contenu fourni.
From: bedoug...@earthlink.net
To: mysql@lists.mysql.com
Subject: a possible group issue???
Date: Fri, 12 Jun 2009 11:36:35 -0700
Hi...
I have the following...
mysql INSERT INTO ParseScriptTBL VALUES
- ('auburnCourse.py',40,1,1),
- ('auburnFaculty.py',40,2,2),
- ('uofl.py',2,1,3),
- ('uky.py',3,1,4),
- ('ufl.py',4,1,5)
- ;
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql select * from ParseScriptTBL as p join universityTBL as u on
u.ID=p.CollegeID where u.ID=40;
+--+---+--+++
| ScriptName | CollegeID | pTypeID | ScriptID | ID |
+--+---+--+++
| auburnCourse.py | 40 | 1 | 1 | 40 |
| auburnFaculty.py | 40 | 2 | 2 | 40 |
+--+---+--+++
2 rows in set (0.00 sec)
i'd like to have a query that gives me both scripts for the college in the
same row...
keeping in mind that some colleges will have no scripts, some will have
only
one, and some will have both...
i've tried to do the query, and added a group by CollegeID' with no
luck..
so how can i combine the two rows to get a single row??
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com
Insert movie times and more without leaving Hotmail®. See how.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
group by different time period than functions allow
Can someone point a link, or show an example. basically, i have something like select week(mydate), count(mystuff) from table group by week(mydate); however, I need week to start on Wed 9am and end next Wed. What's the easiest way to accomplish that? thanks, andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: group by different time period than functions allow
SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW,9 HR) AA) A; This query will produce the previous Wed at 9AM to the next Wed 9AM. Run it in the MySQL Client and note the output: lw...@localhost (DB information_schema) :: SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW,9 HR) AA) A; +-+-+ | DT1 | DT2 | +-+-+ | 2009-06-10 09:00:00 | 2009-06-17 09:00:00 | +-+-+ 1 row in set (0.00 sec) Note the subquery SELECT 4 DOW,9 HR The DAYOFWEEK function returns 1 for Sun, 2 for Mon, 3 for Tue, 4 for Wed, ..., 7 for Sat If you need Fri to Fri change the DOW to 5 If you need Mon to Mon change the DOW to 2 If you need 3AM to 3AM change the HR to 3 Applying it to your query, it should look something like this select week(A.mydate),count(A.mystuff) from table A, (SELECT DT DT1,DATE_ADD(DT,INTERVAL 1 WEEK) DT2 FROM (SELECT DATE_ADD(DATE(DATE_ADD(NOW(),INTERVAL (DOW-DAYOFWEEK(NOW())) DAY)),INTERVAL HR HOUR) DT FROM (SELECT 4 DOW, 9 HR) AA) AAA) B Where A.mydate = B. DT1_9AM And A.mydate B. DT2_9AM group by week(A.mydate); Give it a Try !!! Rolando A. Edwards MySQL DBA (CMDBA) 155 Avenue of the Americas, Fifth Floor New York, NY 10013 212-625-5307 (Work) 201-660-3221 (Cell) AIM Skype : RolandoLogicWorx redwa...@logicworks.net -Original Message- From: Andrey Dmitriev [mailto:admitr...@mentora.biz] Sent: Thursday, June 11, 2009 11:30 AM To: mysql@lists.mysql.com Subject: group by different time period than functions allow Can someone point a link, or show an example. basically, i have something like select week(mydate), count(mystuff) from table group by week(mydate); however, I need week to start on Wed 9am and end next Wed. What's the easiest way to accomplish that? thanks, andrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
