Re: using count in other column

Hello again,

Le 25/07/09 0:41, nha a écrit :
> 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). [...]

I forgot to mention a solution for using the value aliased by
"Monthly_count". An operational way is to reuse the whole aliased
expression, ie. count("tblArtnrs"."Artikelnr") here.

For example, the following query is wrong:
SELECT
"Artnr_ID",
to_char("Date_plan", 'YYYY') AS "Jaar",
to_char("Date_plan", 'MM') AS "Maand",
count("tAr"."Artikelnr") AS "Monthly_count",
"val1", "val2", "val3",
"Monthly_count" + "val1" + "Monthly_count" * "val2" + "Monthly_count" *
"val3"
FROM (
(
"tblAnalyses" AS "tAn"
INNER JOIN "tblStudies" AS "tS" ON "tAn"."Studie_ID" = "tS"."Studie_ID"
)
INNER JOIN "tblFichenr" AS "tF" ON "tS"."ID_fichenr" = "tF"."ID"
)
INNER JOIN "tblArtnrs" AS "tAr" ON "tF"."ID_Art_nrs" = "tAr"."Artnr_ID"
GROUP BY "Artnr_ID", "Date_plan", "val1", "val2", "val3"

It can be rewritten as follows:
SELECT
"Artnr_ID",
to_char("Date_plan", 'YYYY') AS "Jaar",
to_char("Date_plan", 'MM') AS "Maand",
count("tAr"."Artikelnr") AS "Monthly_count",
"val1", "val2", "val3",
count("Artikelnr") + "val1" + count("Artikelnr") * "val2" +
count("Artikelnr") * "val3"
FROM (
(
"tblAnalyses" AS "tAn"
INNER JOIN "tblStudies" AS "tS" ON "tAn"."Studie_ID" = "tS"."Studie_ID"
)
INNER JOIN "tblFichenr" AS "tF" ON "tS"."ID_fichenr" = "tF"."ID"
)
INNER JOIN "tblArtnrs" AS "tAr" ON "tF"."ID_Art_nrs" = "tAr"."Artnr_ID"
GROUP BY "Artnr_ID", "Date_plan", "val1", "val2", "val3"

Aliases have been used to make the overall query expression clearer and
shorter.

Regards.
--
nha / Lyon / France.