Referencing an expression

["Dispensa, Brad" <DispensaB(at)anesthesia(dot)ucsf(dot)edu>]

Hello,

First my apologies for a trivial question, I'm still on the new side of
Postgres coming from many years of access database development.

 

My question is, in an access query you can define a query expression and
then reference that expression again later in the query:

 

Example

 

Select 1_quantity, 1_cost,

[1_quantity]*[cost] as 1_subtotal, 

2_quantity, 2_cost, 

[2_quantity]*[cost] as 2_subtotal, 

[1_subtotal]+[ 2_subtotal] as total

From table

 

In the example above I have a table with purchasing information.  I can
create a subtotal of items using an expression, and then reference those
newly created expressions to create another expression which is the
"total".

 

Using Postgres if I tried the following:

SELECT 

1_quantity, 

1_cost,

1_quantity*cost as 1_subtotal, 

2_quantity, 

2_cost, 

2_quantity*cost as 2_subtotal, 

1_subtotal+ 2_subtotal as total

From table

 

The query will fail stating that it does not know what 1_subtotal or
2_subtotal are.

 

I can not quite figure out how this is done in Postgres with out using
multiple sub quires which makes for long SQL statements.  Can anyone
shed some light on this?

 

Many thanks!

 

Brad Dispensa

-----------------------
University of California San Francisco
Center for Cerebrovascular Research

Institute for Human Genetics
Department of Anesthesia
Phone (415) 206-3334
Fax (415) 206-8907