Обсуждение: using count in other column

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

-- 
View this message in context: http://www.nabble.com/using-count-in-other-column-tp24622738p24622738.html
Sent from the PostgreSQL - sql mailing list archive at 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.

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.