Referencing an expression

From: "Dispensa, Brad" <DispensaB(at)anesthesia(dot)ucsf(dot)edu>
To: sfpug(at)postgresql(dot)org
Subject: Referencing an expression
Date: 2008-08-19 16:36:49
Message-ID: 319CB5A5FC297A43944F5939EF6A96C30829698F@sfgh05.som.ucsf.edu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: sfpug

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

Responses

Browse sfpug by date

  From Date Subject
Next Message David Fetter 2008-08-19 17:21:20 Re: Referencing an expression
Previous Message Jeff Frost 2008-08-15 19:11:40 Re: Who in the Bay Area is a PostgreSQL consultant?