December 1, 2010 at 4:12 pm
HI
I´ve these tables
BO, BI, FN and U_IDINET
I need to group and sum several fields whithin this tabels
BO and BI can be joined by BO.BOSTAMP=BI.BOSTAMP
BO and FN can be joined by BO.FREF=FN.FNFREF
and U_EDINET can be joined to FN or BO with U_IDINET.NUMPROJ=BO.FREF or FN.FNREF.
I´ve tryed
SELECT DISTINCT BO.FREF,BI.ref,SUM(BI.edebito) as soma FROM bo
LEFT JOIN bi ON bo.bostamp=BI.bostamp
LEFT JOIN u_edinet ON bo.fref=u_edinet.numproj
WHERE bo.fref=595
GROUP BY bo.FREF,BI.REF,BI.edebito
ORDER BY BI.REF
but the data returned is not acurate
595 0.000000
595625 451.360000
595625 930.000000
595625 1233.800000
59563 5326.420000
59563 12568.640000
59563 16553.070000
59563 16731.940000
I need something like this
ProjectNumber | REF | VALUE
595 625 1000 ( sum of all the values that are on the tables BI,FN,U_EDINET from ref 595)
....
please HELP me, is it possible to make a selection from records that match into a new table, by this way i would have a table with data from the tables
December 1, 2010 at 4:17 pm
What about the second column value of 625, how did you pick this one out of the possible values from within the ref 595 group? Do you want the highest? If so, try this?
SELECT BO.FREF,
MAX(BI.ref),
SUM(BI.edebito) as soma
FROM bo
LEFT JOIN bi ON bo.bostamp=BI.bostamp
LEFT JOIN u_edinet ON bo.fref=u_edinet.numproj
WHERE bo.fref=595
GROUP BY bo.FREF
ORDER BY BI.REF
December 1, 2010 at 4:27 pm
carlos cachulo-318532 (12/1/2010)
HII´ve these tables
BO, BI, FN and U_IDINET
I need to group and sum several fields whithin this tabels
BO and BI can be joined by BO.BOSTAMP=BI.BOSTAMP
BO and FN can be joined by BO.FREF=FN.FNFREF
and U_EDINET can be joined to FN or BO with U_IDINET.NUMPROJ=BO.FREF or FN.FNREF.
I´ve tryed
SELECT DISTINCT BO.FREF,BI.ref,SUM(BI.edebito) as soma FROM bo
LEFT JOIN bi ON bo.bostamp=BI.bostamp
LEFT JOIN u_edinet ON bo.fref=u_edinet.numproj
WHERE bo.fref=595
GROUP BY bo.FREF,BI.REF,BI.edebito
ORDER BY BI.REF
but the data returned is not acurate
595 0.000000
595625 451.360000
595625 930.000000
595625 1233.800000
59563 5326.420000
59563 12568.640000
59563 16553.070000
59563 16731.940000
I need something like this
ProjectNumber | REF | VALUE
595 625 1000 ( sum of all the values that are on the tables BI,FN,U_EDINET from ref 595)
....
please HELP me, is it possible to make a selection from records that match into a new table, by this way i would have a table with data from the tables
I need something like this
ProjectNumber | REF | VALUE
595 625 1000 ( sum of all the values that are on the tables BI,FN,U_EDINET from ref 595)
this is just an example tables BI,FN and U_EDINET had several records that i need
ex:
ProjectNumber | REF | VALUE
595 625 1000
595 63 1500
595 200 50
thanks for you fast reply
December 1, 2010 at 4:38 pm
How are you getting a sum of 1000 for that? I think it would help if you gave an example of your table DDL, and a few sample rows for each table and an example of how you would expect the data to look.
December 1, 2010 at 4:59 pm
ok, with new values PROJECT = 888
_TABLES BO and BI
select bo.fref,bi.ref,bi.edebito from bo
inner join BI on bo.bostamp=bi.bostamp where bo.fref=888
returns:
888625 100.000000
8886221 200.000000
88863 300.000000
888625 500.000000
_TABLE FN
select fn.fnfref,fn.ref,fn.epv from fn where fn.fnfref=888
returns:
888 625 50.00
888 63 100.00
888 625 50.00
_TABLE U_EDINET
select u_edinet.numproj,u_edinet.ref,u_edinet.importe from u_edinet where u_edinet.numproj=888
returns:
888 625 100.00
888 625 50.00
888 63 200.00
The final result should be:
PROJECT REF SOMA
888 625 850.00
888 63 600.00
888 6221 200.00
colum SOMA should be the sum of all values from the 3 tables SUM(BI.EDEBITO+FN.EPV+U_EDINET.IMPORTE) grouped by ref
thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply