Re: using count in other column

From: nha <lyondif02(at)free(dot)fr>
To: Bartjoosen <bartjoosen(at)hotmail(dot)com>
Cc: PgSQL-sql <pgsql-sql(at)postgresql(dot)org>
Subject: Re: using count in other column
Date: 2009-07-24 22:41:10
Message-ID: 4A6A3886.3080104@free.fr
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

In response to

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message nha 2009-07-24 23:13:07 Re: using count in other column
Previous Message Joshua Tolley 2009-07-24 18:09:56 Re: Bit by "commands ignored until end of transaction block" again