--- In firebird-support@yahoogroups.com, "Adam" <s3057043@...> wrote:
>
> --- In firebird-support@yahoogroups.com, "mthendley" <mt_hendley@>
> wrote:
> >
> > When using interbase, I used the following query:
> >
> > SELECT in_inDate,
> > COUNT(*) AS HowMany,
> > (SELECT SUM(in_MoneyColl) FROM InDetail ID
> > WHERE IM.InvMaster_ID = ID.InvMaster_ID) AS Amt
> > FROM InvMaster IM
> > WHERE IM.inm_InvState = 2
> > AND inm_InvDate >= :BeginDate
> > AND inm_invDate < :EndDate
> > GROUP BY inm_InvDate
> >
>
> I am guessing this query is disallowed because of the ambiguity in the
> non grouped field that is not part of the aggregate. All non aggregate
> fields in the select must be accounted for in the group by. You have a
> field IM.in_indate which does not appear in the group by. You are also
> grouping by a field IM.inm_InvDate which is not included in your
> select, which although you are allowed to do that, I don't see the
point.
>
> How about?
>
> SELECT IM.in_inDate,
> COUNT(DISTINCT IM.InvMaster_ID) AS HowMany,
>
> SUM(ID.in_MoneyColl) AS Amt
> FROM InvMaster IM
> JOIN InDetail ID
> ON (IM.InvMaster_ID = ID.InvMaster_ID)
> WHERE IM.inm_InvState = 2
> AND IM.inm_InvDate >= :BeginDate
> AND IM.inm_invDate < :EndDate
> GROUP BY IM.in_inDate, IM.inm_InvDate
>
> Adam
>
>
>
> > Firebird will not allow this since the subquery is not an aggregate
> > function and not in the GROUP BY clause. Any suggestions on how to
> > migrate this query over to Firebird?
> > Thanks
> >
>