Thread: 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.