CODEWITHSUNDEEP
mysqlsqlsubquery
Pete
Pete

Reputation: 7579

How to store expressions in variables and reuse them in a single MySQL query

So let's say that I have a database with the following table:

--------------------------
id  | arg1 | arg2 |  arg3
--------------------------
1   |   7  |   3  |   9  |
2   |   5  |   9  |   5  |

And let's say I want to do a SUM query that figures some calculations, such as:

SELECT SUM( ( arg1 + arg2 + arg3 ) / ( arg1 + arg2 + arg3 ) * ( arg1 + arg2 + arg3 ) )

This would be a lot simpler if there was a way to somehow store ( arg1 + arg2 + arg3 ) in a variable (let's say, called arg_sum), and then we could do:

SELECT SUM( arg_sum / arg_sum * arg_sum )

I know this is a pretty contrived example, but I've run into real world scenarios where it would really make things more readable if I could store equations in variables and reuse them. Is that possible?

Something maybe like this (except I ideally wouldn't want added included in the result set):

SELECT SUM( arg1 + arg2 + arg3 ) as arg_sum, SUM( arg_sum / arg_sum * arg_sum ) FROM my_table;

Upvotes: 0

Views: 258

Answers (3)

Muhammad Vakili
Muhammad Vakili

Reputation: 708

You can just use a temporary table like this:

Let's say your main table's name is args

CREATE TEMPORARY TABLE TempSums 
SELECT 
      args.id,
      arg1 + arg2 + arg3 as `sum` from args

Now, you can use TempSums Table like this:

SELECT SUM( ( `sum` ) / ( `sum` ) * ( `sum` ) ) FROM TempSums

Upvotes: 1

The Impaler
The Impaler

Reputation: 48810

You can use compute their values and name them in a table expression that you use in the 'FROM' clause. For example:

create table abc (
  id int,
  arg1 int,
  arg2 int,
  arg3 int
);

insert into abc (id, arg1, arg2, arg3) values (1, 7, 3, 9); -- sum = 19
insert into abc (id, arg1, arg2, arg3) values (2, 4, 9, 5); -- sum = 18

select *, sum(arg_sum  / arg_sum * arg_sum) as result
  from (
    select *, (arg1 + arg2 + arg3) as arg_sum from abc
  ) x
  where id = 1;

Result:

id  arg1  arg2  arg3  arg_sum  result
--  ----  ----  ----  -------  ------
1   7     3     9     19       19

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

By way of example...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL
,arg INT NOT NULL
,val INT NOT NULL
,PRIMARY KEY(id,arg)
);

INSERT INTO my_table VALUES
(1,1,7),
(1,2,3),
(1,3,9),
(2,1,5),
(2,2,9),
(2,3,5);


SELECT SUM(arg_sum/(arg_sum*arg_sum)) 
  FROM 
     ( SELECT id, SUM(val) arg_sum FROM my_table GROUP BY id)n;
+--------------------------------+
| SUM(arg_sum/(arg_sum*arg_sum)) |
+--------------------------------+
|                         0.1053 |
+--------------------------------+

Upvotes: 0

Related Questions