At 07:30 AM 14/01/2007, you wrote:

>I'm trying to create the following view:
>
>create view VIEW_COMMITMENTS_LEFT ( ID_MEMBER, ID_DONATION,
>TOTAL_MONTHS_PAID, MONTHS_COMMITED )
>as
>select
> donation_commitments.id_member,
> donation_commitments.id_donation,
> COUNT(commitments_paid.month_paid),
> donation_commitments.months_commited
>from
> donation_commitments,
> commitments_paid
>where
> (commitments_paid.id_member = donation_commitments.id_member) AND
> (commitments_paid.id_donation = donation_commitments.id_donation) AND
> (donation_commitments.done <> 1)
>
>
>I get the following error:
>Invalid expression in the select list (not contained in either an
>aggregate function or the GROUP BY clause).
>
>
>Now I assume this is due to the COUNT, since without it I can create
>the view, now any suggestions on how to get this to work or is there
>some other error I can't see?

Well, the error that is reported explains the error. An aggregate
function needs a GROUP BY clause. Count(aFieldName) is an aggregate function.

I strongly recommend that you avoid such informal syntaxes as you
have used here and stick to the more robust formal
specifications. To return the set I think you want, the syntax should be:

create view VIEW_COMMITMENTS_LEFT
( ID_MEMBER,
ID_DONATION,
TOTAL_MONTHS_PAID,
MONTHS_COMMITED )
as
select
dc.id_member,
dc.id_donation,
COUNT(cp.month_paid),
dc.months_commited
from
donation_commitments dc
join commitments_paid cp
on
cp.id_member = dc.id_member
AND cp.id_donation = dc.id_donation
group by 1,2,4
having dc.done <> 1

But note that your particular COUNT expression may return a result
that is inconsistent with your expectations...although COUNT(*)
counts all records, COUNT(AFieldname) disregards those records where
the value of AFieldName is null.

./heLen