From: nha <lyondif02@free.fr>
To: Bartjoosen <bartjoosen@hotmail.com>
Cc: PgSQL-sql <pgsql-sql@postgresql.org>
Subject: Re: using count in other column
Date: Sat, 25 Jul 2009 00:41:10 +0200
Message-ID: <4A6A3886.3080104@free.fr> (raw)
In-Reply-To: <24622738.post@talk.nabble.com>
References: <24622738.post@talk.nabble.com>
Hello,
Le 23/07/09 11:59, bartjoosen a écrit :
> Hi,
>
> I made up a query to make a count for each item for each month/year:
> SELECT"Artnr_ID", to_char("Date_plan","YYYY") AS "Jaar",
> to_char("Date_plan","MM") AS "Maand", Count("tblArtnrs"."Artikelnr") AS
> "Monthly_count", "val1","val2","val3"
> FROM (("tblAnalyses" INNER JOIN "tblStudies" ON "tblAnalyses"."Studie_ID" =
> "tblStudies"."Studie_ID") INNER JOIN "tblFichenr" ON
> "tblStudies"."ID_fichenr" = "tblFichenr"."ID") INNER JOIN "tblArtnrs" ON
> "tblFichenr"."ID_Art_nrs" = "tblArtnrs"."Artnr_ID"
> GROUP BY "tblArtnrs"."Artnr_ID", to_char("Date_plan","YYYY") ,
> to_char("Date_plan","MM"), "val1","val2","val3";
>
> Now I want to use the "Monthly_count" value for further calculations with
> other columns.
> I tried to use
> "Monthly_count"+ "val1" + "Monthly_count" * "val2" + "Monthly_count" *"val3"
> But "Monthly_count" is not recognised in my calculations.
>
> How can this be solved?
>
> Thanks
>
> Bart
>
The error message you meet is missing in your report although it could
surely help in accurate analysis. However I guess it is about using
alias (like "Monthly_count") for defining project columns (like the one
you tried and failed). Alias columns are used to rename displayed
columns; they cannot be used as terms of other projected columns but
they can be used within GROUP BY clauses. For example, the given GROUP
BY clause may be rewritten (simplier) as:
GROUP BY "Artnr_ID", "Artnr_ID", "Jaar", "Maand", "val1", "val2", "val3"
and even as:
GROUP BY "Artnr_ID", "Date_plan", "val1", "val2", "val3"
as "Jaar" and "Maand" refer to column "Date_plan" which is not passed to
aggregating functions but is effectively aggregated.
Hoping this is a track for you.
Regards.
--
nha / Lyon / France.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: lyondif02@free.fr, bartjoosen@hotmail.com
Subject: Re: using count in other column
In-Reply-To: <4A6A3886.3080104@free.fr>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox