Hello Frank!

I don't know the SQL standard, but I believe CValuesWrong produces a
sensible result. Think of your SQL without any SUM or GROUP BY:

SELECT
a.ID, a.A1,
(b.B1 * b.B2) AS ValueB,
(c.C1) AS ValueC
FROM
TableA a
LEFT JOIN TableB b ON b.AID=a.ID
LEFT JOIN TableC c ON c.AID=a.ID

That should return

1, 'Sample', 1.0, 1
1, 'Sample', 1.0, 1

I don't think it is wrong that summing this gives 2 for the last
column...

To do what you seem to be after, do subselects:

CREATE VIEW BCValuesSubselect (
AID,
A1,
SumB,
SumC
) AS
SELECT
a.ID, a.A1,
(SELECT SUM(b.B1 * b.B2) FROM TableB b WHERE b.AID=a.ID) AS SumB,
(SELECT SUM(c.C1) FROM TableC c WHERE c.AID=a.ID) AS SumC
FROM
TableA a;

HTH,
Set

--- In firebird-support@yahoogroups.com, "Frank" wrote:
> CREATE VIEW CValuesWrong (
> AID,
> A1,
> SumB,
> SumC
> ) AS
> SELECT
> a.ID, a.A1,
> SUM(b.B1 * b.B2) AS SumB,
> SUM(c.C1) AS SumC
> FROM
> TableA a
> LEFT JOIN TableB b ON b.AID=a.ID
> LEFT JOIN TableC c ON c.AID=a.ID
> GROUP BY
> a.ID, a.A1;
>
> INSERT INTO TableA VALUES(1, 'Sample');
> INSERT INTO TableB VALUES(1, 1, 1, '1.0');
> INSERT INTO TableB VALUES(2, 1, 1, '1.0');
> INSERT INTO TableC VALUES(1, 1, '1.0');