Syntax Question -- Multiplying named columns

[arenaninja]

Alright, I'm producing accounting reports based on usage, and I'm trying to get the following snippet to work:

TIMESTAMPDIFF(MINUTE,timeStart,timeEnd)/60 AS Time,
                    feetable.rate AS `Rate`
                    feetable.rate*Time AS `Charge` 

I'm using MySQL 5.5.

 

The column `Time` I'm guessing is floating point (usualy 0.5, 1.5, etc.) whereas `Rate` is Decimal. I get a 'syntax error or access violation' on this one, and I have an idea of why (I'm guessing named columns aren't set until you get past the WHERE statement -- i.e.: I could use them to sort, but not to multiply each other?) However I have little recourse as I do not want to overstress the database by re-calculating the TIMESTAMPDIFF. Any suggestions to get this to work?

[xyph]

Syntax error or access violation? Doesn't seem like a MySQL error to me.

 

Can you be more specific in the table your dealing with, and the results you want?

[arenaninja]

Sorry, seems that's the PHP error. Here's the MySQL error:

ERROR 1054 (42S22): Unknown column 'Time' in 'field list'

The table I have stores information to charge customers -- the relevant ones are:

| table | CREATE TABLE `table` (
  `tableID` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `timeStart` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `timeEnd` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  `rateID` int(10) unsigned NOT NULL,
  PRIMARY KEY (`pkResID`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 |

where rateID is a foreign key. The result I want looks like this (precision notwithstanding, for now):

+-------+--------+------------+
| rate  | Time   | Total      |
+-------+--------+------------+
| 32.50 | 8.0000 | 260.000000 |
| 32.50 | 9.0000 | 292.500000 |
| 32.50 | 1.0000 |  32.500000 |
| 32.50 | 1.0000 |  32.500000 |
| 32.50 | 1.0000 |  32.500000 |

However the code to produce this was:

SELECT feetable.rate,TIMESTAMPDIFF(MINUTE,timeStart,timeEnd)/60 AS Time,rate*TIMESTAMPDIFF(MINUTE,timeStart,timeEnd)/60 AS Total FROM table LEFT JOIN feetable ON (table.rateID=feetable.pkFee);

Note that TIMESTAMPDIFF(MINUTE,timeStart,timeEnd)/60 appears twice on this query -- once to list it AS Time and once to multiply it with the rate. All I'm looking to do is take the value Time and multiply by rate without having to have MySQL recalculate it. In essence, I want to do this:

SELECT feetable.rate,TIMESTAMPDIFF(MINUTE,timeStart,timeEnd)/60 AS Time,rate*Time AS Total FROM table LEFT JOIN feetable ON (table.rateID=feetable.pkFee);

Which MySQL points out is incorrect because the field 'Time' doesn't exist in either table. How do I reference the column 'Time' that I just created?

 

Edit: note that I don't really have a table named "table". I just renamed it as such when posting this for obvious reasons

[xyph]

You have to use variables

 

mysql> SELECT num1, num2,
    ->   @total := (num1+num2) as total,
    ->   (@total * 2) as doubletotal
    -> FROM sometable;
+------+------+-------+-------------+
| num1 | num2 | total | doubletotal |
+------+------+-------+-------------+
|    5 |   10 |    15 |          30 |
+------+------+-------+-------------+
1 row in set (0.00 sec)

 

http://dev.mysql.com/doc/refman//5.5/en/problems-with-alias.html

[arenaninja]

I swear I looked through the documentations for at least an hour, but I could only think of terms like 'AS command' (including in teh google search ingin), and it never occurred to me that the second portion of that statement could be called an Alias. I suppose it's one of the many issues of being self-taught.

 

The solution you've provided, xyph, works just as you posted -- thank you sir.

[arenaninja]

Sorry xyph, but it seems that PDO statements do not play well with MySQL when using variables in queries. Any advice?

[Barand]

Having benchmarked

SELECT (TIMESTAMPDIFF(MINUTE,timestart,timeend)/60) as tm,
                    rate,
                    rate*(TIMESTAMPDIFF(MINUTE,timestart,timeend)/60) AS `Charge`
                    FROM feetable

 

against

 

SELECT @a:=(TIMESTAMPDIFF(MINUTE,timestart,timeend)/60),
                    rate,
                    rate*@a AS `Charge`
                    FROM feetable

 

the first one, where the function is repeated and no variables, came out approx 2 times faster over 1000 calls (0.08 sec vs 0.14 sec) so there seems to be no overhead in repeating the function

[arenaninja]

Having benchmarked

SELECT (TIMESTAMPDIFF(MINUTE,timestart,timeend)/60) as tm,
                    rate,
                    rate*(TIMESTAMPDIFF(MINUTE,timestart,timeend)/60) AS `Charge`
                    FROM feetable

 

against

 

SELECT @a:=(TIMESTAMPDIFF(MINUTE,timestart,timeend)/60),
                    rate,
                    rate*@a AS `Charge`
                    FROM feetable

 

the first one, where the function is repeated and no variables, came out approx 2 times faster over 1000 calls (0.08 sec vs 0.14 sec) so there seems to be no overhead in repeating the function

Oh!! Could this be the effect of MySQL's cache? Because that never occurred to me