SUM function
I need the SUM of a SUM function.
This is working perfect:
SELECT DISTINCT nav.navperunit, Invest_History.client_acct, customers.subunit, Invest_History.redemption_unit, Invest_History.addition_unit, Invest_History.transfer_unit, format(((customers.subunit - SUM(Invest_History.redemption_unit) + SUM(Invest_History.addition_unit)) * nav.navperunit) ,3) AS TotalPerf
FROM customers, Invest_History, nav
WHERE nav.period = 'colname' AND customers.client_acct = Invest_History.client_acct AND customers.active = 'Y'
GROUP BY Invest_History.client_acct
But I am having problems with the following query:
SELECT DISTINCT nav.navperunit, Invest_History.client_acct, customers.subunit, Invest_History.redemption_unit, Invest_History.addition_unit, Invest_History.transfer_unit, format(((customers.subunit - SUM(Invest_History.redemption_unit) + SUM(Invest_History.addition_unit)) * nav.navperunit) ,3) AS Total
FROM customers, Invest_History, nav
WHERE nav.period = 'colname' AND customers.client_acct = Invest_History.client_acct AND customers.active = 'Y'
GROUP BY Invest_History.client_acct
The only diference betweeen the two queries is that the second one SUM up the first function... I beleived it should be working, but I get an error message saying: "1111 Invalid use of group function." I tried GROUP BY all other posibilities, and I am still getting the same error....