Re: using count in other column
От | nha |
---|---|
Тема | Re: using count in other column |
Дата | |
Msg-id | 4A6A4003.8030203@free.fr обсуждение исходный текст |
Ответ на | Re: using count in other column (nha <lyondif02@free.fr>) |
Список | pgsql-sql |
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.
В списке pgsql-sql по дате отправления: