enotalone
msg:3416082 | 6:59 pm on Aug 7, 2007 (gmt 0) |
the closest I got is the query below. But! I am not sure that by grouping on the city I actually get the correct results and it uses temporary tables and filesort. So even if it works right that is one slow and resource intensive query. SELECT city, state, avg( sale_avg ) AS save FROM store_location
LEFT JOIN item ON (item.store_id = store_location.store_id )
GROUP BY city
ORDER BY save DESC
|
syber
msg:3416723 | 12:39 pm on Aug 8, 2007 (gmt 0) |
Since you are doing a GROUP BY city, you don't need to join the store table. It also should not be necessary to do a LEFT JOIN as there should be a store_location for every item. SELECT city, state, avg( sale_avg ) AS save
FROM store_location INNER JOIN item
ON (item.store_id = store_location.store_id )
GROUP BY city
ORDER BY save DESC
|
enotalone
msg:3417127 | 7:01 pm on Aug 8, 2007 (gmt 0) |
Thanks syber, that seems to do the job. Anything I can do to optimize the query so there are no temporary tebles, filsort when the query is executed?
|
syber
msg:3417153 | 7:20 pm on Aug 8, 2007 (gmt 0) |
I did notice one problem, you should be grouping on city, state - not just city. SELECT city, state, avg( sale_avg ) AS save
FROM store_location INNER JOIN item
ON (item.store_id = store_location.store_id )
GROUP BY city, state
ORDER BY save DESC This shouldn't cause any temp tables and is the most efficent solution. Having an index on city,state will probably speed the query up.
|
enotalone
msg:3417157 | 7:25 pm on Aug 8, 2007 (gmt 0) |
Thank you very much Syber.
|
|
