I'm trying to play a Farm Rentals application, made with FileMaker in SQLite.
I want to obtain, from each farm and year, the income and expenses. The tables involved are the following: Fincas (Farms), Lloguers (Income), Despeses (Expenses).
I used the following query:
SELECT f.finca, l.anyo año, sum(l.total) Ingresos, sum(d.total) Gastos
FROM Finques f
JOIN Lloguers l ON l.fincaID = f.fincaID AND l.anyo = d.anyo
JOIN Despeses d ON f.fincaID = d.fincaID AND l.anyo = d.anyo
GROUP BY f.finca, l.anyo
ORDER by f.finca, l.anyo
Revenue totals are multiplied by the number of expense records and vice versa. With the same formula, making income and expenses separately works
I enclose the totals grouped and individually.
Thanks in advance
fincaID,serial,referencia,finca,municipi
C6C26524-7025-46D1-B93B-18889180031A, 1, FIN001, City, Mongat 88A42D2B-1FA4-4E90-9EE9-0F39203FC7C0,2, FIN002, d 'Osona 10, Mongat C3F114C8-050C-444B-84DE-3BDDAD118B5B, 3, FIN003, d 'Osona 8, Mongat
lloguerID,finca,anyo,total,fincaID,pisID,llogaterID
5237DB36-F6DA-4648-A30D-B05E7DE34124, d 'Osona 8,2009,1134.04, C3F114C8-050C-444B-84DE-3BDDAD118B5B, 3AAFFCB8-716B-4A36-A13E-D18223372239, 5EE7A99E-61A0-41F1-8D2D-FA32667C9155, d 'Osona 10,2009,1136.95,88A42D2B-1FA4-4E90-9EE9-0F39203FC7C0, C314C03A-F621-4866-AD4D-80BA0B3EB77A, 69B45437-090C-4A26-801F-AC93D80D8836
despesaID,finca,total,proveidorID,fincaID,pisID,anyo
C7C5D5D8-C4CC-4E97-8756-77B8B5D3A574, d 'Osona 10,197.92,1F24813F-B8E5-4D83-B52D-F99E41B48E8C, 88A42D2B-1FA4-4E90-9EE9-0F39203FC7C0, C314C03A-F621-4866-AD4D-80BA0B3EB77A, 2009 EAD02493-6B76-444D-97FB-D554D61D9778, d 'Osona 8,197.92,1F24813F-B8E5-4D83-B52D-F99E41B48E8C, C3F114C8-050C-444B-84DE-3BDDAD118B5B, 3AAFFCB8-716B-4A36-A13E-D18223372239,2009 999800F5-7D61-4658-A273-67430FAEADD0, Sea Village, 21.84,711521CD-0F8A-4A5E-BA3B-3B7895A4D335, C6C26524-7025-46D1-B93B-18889180031A, A80DD749-3F4F-48A8-BEE1-5769776D24B5,2009