--- 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
>