Board index » cppbuilder » select sum() from different tables
Guillermo A. Díaz T.
CBuilder Developer |
Guillermo A. Díaz T.
CBuilder Developer |
select sum() from different tables2004-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. |
Jayme Jeffman Filho
CBuilder Developer |
2004-09-04 01:21:00 AM
Re:select sum() from different tables
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 QuoteHello! |
Guillermo A. Díaz T.
CBuilder Developer |
2004-09-07 02:08:25 AM
Re:select sum() from different tables
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} |
Jayme Jeffman Filho
CBuilder Developer |
2004-09-07 04:59:41 AM
Re:select sum() from different tables
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 QuoteI'm sorry. I attempted it with JOIN, with UNION and with all that I |