pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedusing count in other column
3+ messages / 2 participants
[nested] [flat]
* using count in other column
@ 2009-07-23 09:59 bartjoosen <bartjoosen@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: bartjoosen @ 2009-07-23 09:59 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: using count in other column
@ 2009-07-24 22:41 nha <lyondif02@free.fr>
parent: bartjoosen <bartjoosen@hotmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: nha @ 2009-07-24 22:41 UTC (permalink / raw)
To: Bartjoosen <bartjoosen@hotmail.com>; +Cc: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: using count in other column
@ 2009-07-24 23:13 nha <lyondif02@free.fr>
parent: nha <lyondif02@free.fr>
0 siblings, 0 replies; 3+ messages in thread
From: nha @ 2009-07-24 23:13 UTC (permalink / raw)
To: Bartjoosen <bartjoosen@hotmail.com>; +Cc: 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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2009-07-24 23:13 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-07-23 09:59 using count in other column bartjoosen <bartjoosen@hotmail.com>
2009-07-24 22:41 ` nha <lyondif02@free.fr>
2009-07-24 23:13 ` nha <lyondif02@free.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox