473,287 Members | 1,515 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,287 software developers and data experts.

Column as result of subtraction of two other columns?

Hi everyone,

I'm trying to calculate an output column which is the difference of two
other columns in the query output; the first column is an aggregate of
items in stock, while the second column is an aggregate of items which
have been used. The third column should should be the difference of the
two values so I can then output all three columns in a table.

Unfortunately I can't get this to work at the moment :(. I've simplified
the query down to the following:

dev=# select 1 as a, 2 as b, (b - a) as c;
ERROR: column "b" does not exist
dev=#

Do I need to create some form of alias so the calculation can see the
other columns? I am using PostgreSQL 7.4.2 on Linux.
Many thanks,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
5 13117
On Fri, Jul 16, 2004 at 15:31:33 +0100,
Mark Cave-Ayland <m.***********@webbased.co.uk> wrote:
Hi everyone,

I'm trying to calculate an output column which is the difference of two
other columns in the query output; the first column is an aggregate of
items in stock, while the second column is an aggregate of items which
have been used. The third column should should be the difference of the
two values so I can then output all three columns in a table.

Unfortunately I can't get this to work at the moment :(. I've simplified
the query down to the following:

dev=# select 1 as a, 2 as b, (b - a) as c;
ERROR: column "b" does not exist
dev=#

Do I need to create some form of alias so the calculation can see the
other columns? I am using PostgreSQL 7.4.2 on Linux.


You can't use column aliases in other columns; you need to repeat the
column expressions.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #2

On 16/07/2004 15:31 Mark Cave-Ayland wrote:
Hi everyone,

I'm trying to calculate an output column which is the difference of two
other columns in the query output; the first column is an aggregate of
items in stock, while the second column is an aggregate of items which
have been used. The third column should should be the difference of the
two values so I can then output all three columns in a table.

Unfortunately I can't get this to work at the moment :(. I've simplified
the query down to the following:

dev=# select 1 as a, 2 as b, (b - a) as c;
ERROR: column "b" does not exist
dev=#

Do I need to create some form of alias so the calculation can see the
other columns? I am using PostgreSQL 7.4.2 on Linux.


I think you can use a sub-select (this works for me on 7.3.4):

select a, b, (b - a) as c from (select .... as a, .... as b from mytable)
as sub;

HTH

--
Paul Thomas
+------------------------------+---------------------------------------------+
| Thomas Micro Systems Limited | Software Solutions for
Business |
| Computer Consultants |
http://www.thomas-micro-systems-ltd.co.uk |
+------------------------------+---------------------------------------------+

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #3
Try

select a, b, (b - a) as diff from (
select 1 as a, 2 as b
) as tmp;

John Sidney-Woollett

Bruno Wolff III wrote:
On Fri, Jul 16, 2004 at 15:31:33 +0100,
Mark Cave-Ayland <m.***********@webbased.co.uk> wrote:
Hi everyone,

I'm trying to calculate an output column which is the difference of two
other columns in the query output; the first column is an aggregate of
items in stock, while the second column is an aggregate of items which
have been used. The third column should should be the difference of the
two values so I can then output all three columns in a table.

Unfortunately I can't get this to work at the moment :(. I've simplified
the query down to the following:

dev=# select 1 as a, 2 as b, (b - a) as c;
ERROR: column "b" does not exist
dev=#

Do I need to create some form of alias so the calculation can see the
other columns? I am using PostgreSQL 7.4.2 on Linux.

You can't use column aliases in other columns; you need to repeat the
column expressions.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)


---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #4
Mark Cave-Ayland wrote:
Hi everyone,

I'm trying to calculate an output column which is the difference of two
other columns in the query output; the first column is an aggregate of
items in stock, while the second column is an aggregate of items which
have been used. The third column should should be the difference of the
two values so I can then output all three columns in a table.

Unfortunately I can't get this to work at the moment :(. I've simplified
the query down to the following:

dev=# select 1 as a, 2 as b, (b - a) as c;
ERROR: column "b" does not exist
dev=#

Do I need to create some form of alias so the calculation can see the
other columns? I am using PostgreSQL 7.4.2 on Linux.
You can can try:

select a, b, a-b from
( select sum( x) as a, sum( y) as b from whatever group by z);

You can also do:

select sum( x), sum( y), sum(x-y) from whatever group by z;

HTH

Many thanks,

Mark.

---

Mark Cave-Ayland
Webbased Ltd.
Tamar Science Park
Derriford
Plymouth
PL6 8BX
England

Tel: +44 (0)1752 764445
Fax: +44 (0)1752 764446
This email and any attachments are confidential to the intended
recipient and may also be privileged. If you are not the intended
recipient please delete it from your system and notify the sender. You
should not copy it or use it for any purpose nor disclose or distribute
its contents to any other person.

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #5
On Fri, 16 Jul 2004 12:04:54 -0400, Tom Lane <tg*@sss.pgh.pa.us> wrote:
select sum(x), sum(y), sum(x)-sum(y) from ...

At least since 7.4, the system will notice the duplicate aggregates
and run only two summations to compute the above, followed by a single
subtraction at the end. The apparently more intelligent way suggested
by Jean will have to run three summations, and thus end up being a net
loss.


Also note that Jean-Luc's
select sum( x), sum( y), sum(x-y) from whatever group by z;
gives a different result in the presence of NULLs.

Servus
Manfred

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 23 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Prit | last post by:
Hi everyone I guess this should be a simple question for the gurus I have a Data in a column which is to be places in 2 columns instead of one. How do i go about doing it in MS SQL server? Could...
3
by: olanorm | last post by:
I have a query where one or more of the columns returned is a result from a subquery. These columns get their own alias. I want to filter out the rows containing NULL from the subqueries but it...
0
by: andrewcw | last post by:
After I made a nice application with WINFORM I tried to apply much of the same code, but there are lost of differences. I load the grid colors, column width, column header from a xml data file....
0
by: moondaddy | last post by:
using vb.net I have a datalist where the Repeat layout has: Columns 2 Direction Horizontal Layout Table I want to put a spacer column inbetween the 2 columns for better control of...
1
by: trizub | last post by:
I have a table of populations of cities (identified by country, state, cities) on particular dates. How do I transpose the date values in my rows to a date value column that lists populations for...
2
by: Dscar | last post by:
Hi, I am a beginner in ACCESS, I've imported data into access, and then realized that I need to split the information in one of my columns into 2 columns. the information looks like this:...
2
by: whitsey | last post by:
I have a a table which has the following columns: PART_NUMBER RESULT_TYPE ..... There are 10-15 different "result types", i.e. -1, 0, 1, 2, 3, 4, 5, 6, 7, 8, 9... What I want is to...
2
by: nico3334 | last post by:
Hi, I have a SQL table that has data like this: Title Month Info 1 ---- 7 ---- 100 1 ---- 7 ---- 100 1 ---- 8 ...
1
by: miferdin | last post by:
Hi friends, 1. I have a datagrid with 5 columns, in this when i click edit in a row, all the columns changes into textbox but what i need is to make only a single column into a text...
1
by: prasadgopathi | last post by:
hi friends, i have one table is normal column is bit id isnormal 1 0 2 0 3 0 4 1
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.