CODEWITHSUNDEEP
sqlsql-server
drab
drab

Reputation: 494

SQL Reuse Select Clause with Variables

How can I reuse Select Calculations?

Currently, my select looks like this:

SELECT
  (sum(TOT1) - sum(TOT2) - sum(TOT3)) * 0.1 as TOTperc1,
  (sum(TOT1) - sum(TOT2) - sum(TOT3)) * 0.2 as TOTperc2
FROM ....

How can I reuse the summary of this calculation as a variable so I can use in both Selects?

(sum(TOT1) - sum(TOT2) - sum(TOT3))

So the final Select Would look like this:

DECLARE @TOT as something = sum(TOT1) - sum(TOT2) - sum(TOT3);

SELECT
   @TOT * 0.1 as TOTperc1,
   @TOT * 0.2 as TOTperc2
FROM ....

UPDATE: The question had mysql as a tag by mistake, and you may find answers that are not relevant to mssql, but they may be correct for mysql

Upvotes: 0

Views: 328

Answers (2)

Serg
Serg

Reputation: 22811

You can wrap it in a subquery

select s * 0.1 as TOTperc1,
       s * 0.2 as TOTperc2
FROM (
  SELECT
    (sum(TOT1) - sum(TOT2) - sum(TOT3)) s
  FROM ....
) t

Upvotes: 2

Martin
Martin

Reputation: 7

If I understand your question correctly you want to have

(sum(TOT1) - sum(TOT2) - sum(TOT3))

as a variable for the two calculation parts.

I would solve this in the following way:

SELECT
   (sum(TOT1) - sum(TOT2) - sum(TOT3)) as total,
   total * 0.1 as TOTperc1,
   total * 0.2 as TOTperc2
FROM ....

Upvotes: -1

Related Questions