Hi,

> Can anyone tell me what the syntax is to sum the data from 4
> different tables?
>
> I have tried:
> SELECT tab1.key1,
> tab1.col11,tot2.sum2,tot3.sum3,tot4.sum4,tot5.sum5
> FROM tab1
> JOIN (SELECT key1,SUM(field) AS sum2
> FROM tab2
> GROUP BY key1) AS tot2
> ON tot2.key1 = tab1.key1
> JOIN (SELECT key1,SUM(field) AS sum3
> FROM tab3
> GROUP BY key1) AS tot3
> ON tot3.key1 = tab1.key1
> JOIN (SELECT key1,SUM(field) AS sum4
> FROM tab4
> GROUP BY key1) AS tot4
> ON tot4.key1 = tab1.key1
> JOIN (SELECT key1,SUM(field) AS sum4
> FROM tab5
> GROUP BY key1) AS tot5
> ON tot5.key1 = tab1.key1

SELECT
tab1.key1,
tab1.col11,
(SELECT SUM(field) FROM tab2 WHERE tab2.key1 = tab1.key1) AS sum2,
(SELECT SUM(field) FROM tab3 WHERE tab3.key1 = tab1.key1) AS sum3,
(SELECT SUM(field) FROM tab4 WHERE tab4.key1 = tab1.key1) AS sum4,
(SELECT SUM(field) FROM tab5 WHERE tab5.key1 = tab1.key1) AS sum5
FROM
tab1

> But when i try and test this it appears to object about the
> first '(select' statement.

Derived tables are not supported in FB1.5 and earlier versions, but for your query you don't need
it.

Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81



--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.8.0 - Release Date: 21-3-2005