Skip to Content.
Sympa Menu

internetworkers - [internetworkers] SQL Help?

internetworkers AT lists.ibiblio.org

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

List archive

Chronological Thread  
  • From: Scott Russell <catfather AT donpoo.net>
  • To: internetworkers AT lists.ibiblio.org
  • Subject: [internetworkers] SQL Help?
  • Date: Sun, 24 Feb 2008 18:11:17 -0500

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





Archive powered by MHonArc 2.6.24.

Top of Page