aggregate function error

[jozino01]

hi,
i have the following query:

SELECT [REVENUE1].[project], nz([REVENUE1].[TOT_REV], 0), [revenue1].[mm], [revenue1].[yy],
[expense1].[project], nz([expense1].[TOT_exp], 0), [expense1].[mm], [expense1].[yy]
FROM [revenue1], [EXPENSE1]
group by [revenue1].[project], nz([revenue1].[tot_rev], 0), [revenue1].[mm], [revenue1].[yy],
[expense1].[project], nz([expense1].[tot_exp], 0), [expense1].[mm], [expense1].[yy]
having ((([REVENUE1].[project])=([EXPENSE1].[project])) And (([REVENUE1].[mm])=([expense1].[mm])) and (([revenue1].[yy])=([expense1].[yy])));

and i am getting "... as part of an aggregate function" error even i am grouping by all items used in select.

can someone help me with this problem, please?

 

[PHV]

And what about this (as you don't use any aggregate function) ?
SELECT DISTINCT R.project, Nz(R.TOT_REV, 0) AS Rev, R.mm, R.yy, E.project, Nz(E.TOT_exp, 0) AS Exp, E.mm, E.yy
FROM [revenue1] AS R INNER JOIN [EXPENSE1] AS E ON R.project = E.project AND R.mm = E.mm AND R.yy = E.yy;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

yes, it works. thanks

 

[jozino01]

i took closer look and noticed that there are missing records for the periods in which only expenses resp. revenue occured.
if there were both revenue and expenses the records are ok.
i tought that using Nz would fix this problem, apparently it doesn't.
is there any way to fix it?

 

[PHV]

Replace this:
FROM [revenue1] AS R INNER JOIN [EXPENSE1] AS E
By this:
FROM [revenue1] AS R RIGHT JOIN [EXPENSE1] AS E

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

it's getting there
now the expenses are correct, just missing some revenue (months with no expenses for the same projects...?)

 

[PHV]

As JetSQL lacks the FULL JOIN you have to play with UNION:
SELECT R.project, Nz(R.TOT_REV, 0) AS Rev, R.mm, R.yy, E.project, Nz(E.TOT_exp, 0) AS Exp, E.mm, E.yy
FROM [revenue1] AS R LEFT JOIN [EXPENSE1] AS E ON R.project = E.project AND R.mm = E.mm AND R.yy = E.yy
UNION SELECT Null, 0, Null, Null, E.project, Nz(E.TOT_exp, 0), E.mm, E.yy
FROM [revenue1] AS R RIGHT JOIN [EXPENSE1] AS E ON R.project = E.project AND R.mm = E.mm AND R.yy = E.yy
WHERE R.project Is Null;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jozino01]

yes, that's perfect. thanks.