Board index » cppbuilder » select sum() from different tables

2004-09-03 02:38:34 AM
cppbuilder60
Hello!
I've got following problem, too:
SELECT P.Obra,SUM(D.TotalProducto-(D.NetoProv*D.Cantidad)) AS Util,
SUM(T.TotalVrHoras) AS Nom
FROM DetPresupuesto D, Presupuestos P,ClasifDiariaHorasTrab T
WHERE D.Fecha>=:FeI AND D.Fecha<=:FeF AND
D.Presupuesto=P.NroPresupuesto AND P.Obra=T.Obra AND
T.Fecha>=:FeI AND T.Fecha<=:FeF
GROUP BY P.Obra ORDER BY P.Obra;
The "Util" sum is well but "Nom" gives superior results to the correct ones.
I have C++ Borland 5 and Paradox 7.
Thank you advanced by any help.
Guillermo.
 

Hello Guillermo,
Remember that the "where" and "group by" clause are applied to the whole
SQL command, so check if your select strategy is according to your needs.
HTH
Jayme.
"Guillermo A. Díaz T." < XXXX@XXXXX.COM >escreveu na mensagem

I'm sorry. I attempted it with JOIN, with UNION and with all that I supposed
it could serve. But I have not been able to obtain right sums.
What I look for is, the corresponding values in one period, to subtract the
SUM () of 2 columns (that are in different tables) to the SUM () of the
multiplication of other 2 columns that yes they are meetings in one of the
mentioned tables. But one of the tables doesn't have the column for the one
which the GROUP BY will be made so it is necessary to relate it with its
master table that yes has it. The tables are approximately:
Presupuesto DetPresupuesto
------------------ -----------------
-------
(PK) NroPresupuesto Alpha(15) ->(PK) Presupuesto Alpha(15)
Obra Alpha(15) (PK) Consec
Autoincrement(+)
Fecha
Date
Cantidad
Number
NetoProv
Money
TotalProducto
Money
NomDiaria
--------------------
-----
(PK) Obra
Alpha(15)
(PK) Fecha
Date
(PK) Trabajador
Long Integer
TotalVrHoras
Money
The table NomDiaria doesn't have any relationship with others 2. But the
GROUP BY needs it for Obra. Therefore the code that I have is:
SELECT
P.Obra,SUM(D.TotalProducto-(D.NetoProv*D.Cantidad)) -SUM(N.TotalVrHoras)
Useful ACE
FROM DetPresupuesto D, Presupposed P,NomDiaria N
WHERE D.FECHA>=:FEI AND D.FECHA <= :FEF AND
D.PRESUPUESTO=P.NROPRESUPUESTO AND P.OBRA=N.OBRA AND
N.FECHA>=:FEI AND N.FECHA <= :FEF
GROUP BY P.OBRA ORDER BY P.OBRA;
Please, can somebody to suggest me the appropriate SELECT, remembering that
it will be made against Paradox?
Thank you.
Guillermo.
 

{smallsort}

Hello Díaz,
If any of the tables do not have any relationship with the others, the join
operation will not be a 'inner' join, so you can have records that do not
match any table and they will be cut off from the result set.
HTH
Jayme.
"Guillermo A. Díaz T." < XXXX@XXXXX.COM >escreveu na mensagem