Nesting Calculations in Select Statements

[jmiskey]

Can I base calculations on other calculations within my Select statement, or do I need to base each calculation on the underlying components?

For example, I have this query

SELECT 
A.SOCIAL_SECURITY_NUM, 
(A.TOTAL_ELECTIONS + A. TOTALOTHERADHOC + A.TOTAL_ROLLOVER) AS ELECTIONS, 
(A.SUMOFANNUAL_EMPLOYEE_PRETAX_CONTRIB + A.SUMOFANNUAL_EMPLOYER_PRETAX_CONTRIB) AS DEPOSITS
FROM 
QRY_MEMBER125_BENEFITS_STATUS AS A

I would like to add one more calculated field to the Select statement, to determine the difference between the Elections and Deposits calculations, i.e.

 (ELECTIONS – DEPOSITS) AS DIFFERENCE

but that doesn’t appear to work.

Do I have to do it like:

(A.TOTAL_ELECTIONS + A. TOTALOTHERADHOC + A.TOTAL_ROLLOVER - A.SUMOFANNUAL_EMPLOYEE_PRETAX_CONTRIB - A.SUMOFANNUAL_EMPLOYER_PRETAX_CONTRIB) AS DIFFERENCE

or is there a shorter way?

 

[ptheriault]

You have to write like your last example.

(A.TOTAL_ELECTIONS + A. TOTALOTHERADHOC + A.TOTAL_ROLLOVER - A.SUMOFANNUAL_EMPLOYEE_PRETAX_CONTRIB - A.SUMOFANNUAL_EMPLOYER_PRETAX_CONTRIB) AS DIFFERENCE

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Nope. In my opinion, that is the best way to do it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jmiskey]

Thanks.

It also appears that I cannot use the Calculations in WHERE clauses, i.e. in my example above I cannot use

WHERE (ELECTIONS - DEPOSITS)>0

Seems like a nuisance to have to keep typing in the same calculations time and again, but it is what it is...

 

[ptheriault]

That is correct. That WHERE clause will fail.

- Paul
- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

The alternative method is to use a subquery, like this...

[COLOR=blue]Select[/color] B.SOCIAL_SECURITY_NUM,
       B.Elections,
       B.Deposits,
       B.Elections - B.Deposits [COLOR=blue]As[/color] [[COLOR=#FF00FF]Difference[/color]]
[COLOR=blue]From[/color]   (
       [COLOR=blue]SELECT[/color] A.SOCIAL_SECURITY_NUM, 
              (A.TOTAL_ELECTIONS + A. TOTALOTHERADHOC + A.TOTAL_ROLLOVER) [COLOR=blue]AS[/color] ELECTIONS, 
              (A.SUMOFANNUAL_EMPLOYEE_PRETAX_CONTRIB + A.SUMOFANNUAL_EMPLOYER_PRETAX_CONTRIB) [COLOR=blue]AS[/color] DEPOSITS
       [COLOR=blue]FROM[/color]   QRY_MEMBER125_BENEFITS_STATUS [COLOR=blue]AS[/color] A
       ) [COLOR=blue]As[/color] B
[COLOR=blue]Where[/color]  (ELECTIONS - DEPOSITS)>0

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[jmiskey]

George,

That actually is exactly what I did, mostly because I am using Subqueries because the code I posted is just a small piece of larger code I am building where I am linking 4 tables together.

I was just curious if there was a way to it within one query, for those instances in which I am only using one table. As a programmer, I always ask "is there an easier or more efficient way". Laziness -- it is what drives us programmers to efficiency!