Same Query MySQL User Defined Variables

Question

I'm attempting to use MySQL's user defined variables, but due to restrictions I can't use them in the same way as it demonstrates, where one query sets the variable and a second uses it. My requirement is to be able to use it multiple times in a query.

Consider the following:

SELECT (Some Really Taxing Calculation) AS Total FROM Purchases
WHERE Total < 55 AND itemName = "Bananas"
OR Total > 90 AND itemName = "Apples"
OR Total = 30 AND itemName = "Peaches"
ORDER BY Total

It'd be really nice to only run the Total calculation once, and then use the result multiple times throughout the query. Right now, the only way I've gotten it to work in one query is to rerun it for every place it's used as Total returns an error saying an unknown column has been used.

If you put it in the FROM clause, wouldn't it only be calculated once, and you can get the value through something like total.Value. — Dirk
– Dirk, Commented Jun 22, 2011 at 22:40
@Ryan Gross, single queries, I can't run multiple queries that relate to each other. — Robert
– Robert, Commented Jun 22, 2011 at 22:42
@Dirk: I'm unfamiliar with the approach you're talking about, if you think it would work and can explain I'll give it a whirl. — Robert
– Robert, Commented Jun 22, 2011 at 22:42
The "taxing calculation", is it returning a (int/float) value or is it somehow returning column names? In the first case I cant see how you are using the value and compare it with another value. — Avada Kedavra
– Avada Kedavra, Commented Jun 22, 2011 at 23:17

MatBailie · Accepted Answer · 2011-06-22 23:15:08Z

1

Imbed the total calculation in a sub-query...

SELECT
  Total
FROM
  (SELECT (Some Really Taxing Calculation) AS Total, * FROM Purchases) AS Purchases
WHERE Total < 55 AND itemName = "Bananas"
   OR Total > 90 AND itemName = "Apples"
   OR Total = 30 AND itemName = "Peaches"
ORDER BY
  Total

Note: The sub-query (inline-view) is expanded by MySQL when generating it's PLAN. Putting brackets around the query doesn't necessarily mean you're forcing it to be stupidly inefficient :)

answered Jun 22, 2011 at 23:15

MatBailie

87.5k19 gold badges112 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dirk · Accepted Answer · 2011-06-22 22:46:25Z

0

SELECT *, total.Value
FROM Purchases, (SELECT COUNT(*) as Value .... Some Really Taxing Calculation) total
WHERE Total < 55 AND itemName = "Bananas"
OR Total > 90 AND itemName = "Apples"
OR Total = 30 AND itemName = "Peaches"
ORDER BY Total

So basically, do your calculation in a nested SELECT type query in the FROM clause and give it a name of total than you can then use else where in your query.

answered Jun 22, 2011 at 22:46

Dirk

3,1032 gold badges37 silver badges40 bronze badges

2 Comments

Robert Over a year ago

Okay, so if it's using a calculation based on rows, I have to recreate that environment within the Total, so basically apply my other where's and joins twice?

Dirk Over a year ago

If you have to in order to get the 'Some Really Taxing Calculation' value, then yes, you'd have to reapply the same wheres a joins. Write your calculation query/statement first (with whatever dependencies it needs), then wrap it with parens and stick it into the FROM clause of another SQL query.

Collectives™ on Stack Overflow

Same Query MySQL User Defined Variables

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related