Internetworkers: http://www.ibiblio.org/internetworkers/

[Scott Russell <catfather AT donpoo.net>]

I need a little help getting my head around an SQL problem. I'm hoping
to find some local help before resorting to the Forums at mysql.com. I
don't see an encouraging reply-to-post ratio on those forums...

I'm using MySQL 5.0.x and doing a join with cross-tab (pivot table)
output. It works great as it is however now I have a requirement to
additional processing on columns created by the cross-tabulation.

So, here is the working query. I want to create a new column named
'concurrency' which is the product of the read_ops and read_data column.
If I simply add in a SELECT statement for "SUM(`read_ops` * `read_data`)
AS `concurrency`" prior to the FROM statement I get an SQL error saying
those columns do not exist. That to me essentially means that they
haven't been created yet so I cannot reference them. 

How do I work around this? Temp tables? Stored Procedures? Variables? A
little of each?

SELECT
perfstat_section.iteration
, latx_stats_data.label
, SUM(IF(latx_stats_data.statname = 'read_ops', `statval`, 0)) AS 'read_ops'
, SUM(IF(latx_stats_data.statname = 'read_data', `statval`, 0)) AS 'read_data'
, SUM(IF(latx_stats_data.statname = 'read_latency', `statval`, 0)) AS 
'read_latency'

FROM (`latx_stats`)
JOIN `perfstat_section` ON perfstat_section.secid = latx_stats.secid
JOIN `latx_stats_data` ON latx_stats.latxid = latx_stats_data.latxid

WHERE latx_stats.perfid = '1'
AND latx_stats.object = 'volume'
AND perfstat_section.hostname = 'ny-filer1'
AND latx_stats_data.statname IN ('total_ops', 'avg_latency', 'read_ops', 
'read_data', 'read_latency') 

GROUP BY `iteration`, `label`
ORDER BY `read_ops` DESC
LIMIT 10;

+-----------+-------------+----------+-----------+--------------+
| iteration | label       | read_ops | read_data | read_latency |
+-----------+-------------+----------+-----------+--------------+
|        12 | NY2_6101SG4 |      240 |   1969480 |         9.04 | 
|        12 | NY2_6104SG1 |      228 |   1871021 |         9.24 | 
|        12 | NY2_6104SG2 |      221 |   1817155 |         8.86 | 
|        12 | NY2_6102SG4 |      220 |   1809272 |         9.09 | 
|        12 | NY2_6104SG5 |      219 |   1796057 |         8.44 | 
|        12 | NY2_6102SG5 |      218 |   1786242 |         8.94 | 
|        12 | NY2_6101SG2 |      218 |   1788715 |         9.04 | 
|        12 | NY2_6102SG1 |      215 |   1762130 |         9.37 | 
|         9 | NY2_6101SG4 |      213 |   1746753 |        10.69 | 
|        12 | NY2_6101SG5 |      213 |   1746518 |         9.29 | 
+-----------+-------------+----------+-----------+--------------+

what I want:

+-----------+-------------+----------+-----------+--------------+-------------+
| iteration | label       | read_ops | read_data | read_latency | concurrency 
|
+-----------+-------------+----------+-----------+--------------+-------------+
|        12 | NY2_6101SG4 |      240 |   1969480 |         9.04 | 
|        12 | NY2_6104SG1 |      228 |   1871021 |         9.24 | 
|        12 | NY2_6104SG2 |      221 |   1817155 |         8.86 | 
|        12 | NY2_6102SG4 |      220 |   1809272 |         9.09 | 
|        12 | NY2_6104SG5 |      219 |   1796057 |         8.44 | 
|        12 | NY2_6102SG5 |      218 |   1786242 |         8.94 | 
|        12 | NY2_6101SG2 |      218 |   1788715 |         9.04 | 
|        12 | NY2_6102SG1 |      215 |   1762130 |         9.37 | 
|         9 | NY2_6101SG4 |      213 |   1746753 |        10.69 | 
|        12 | NY2_6101SG5 |      213 |   1746518 |         9.29 | 
+-----------+-------------+----------+-----------+--------------+

-- 
Scott <catfather AT donpoo.net>
AIM: BlueCame1