List:General Discussion« Previous MessageNext Message »
From:Wakan Date:April 19 2005 6:39pm
Subject:group by problem
View as plain text  
Hi,
I've 2 tables of payments....
1: payment emission (P: --> ID,ID_cli,tot)
2: part payments (A: --> ID,P_ID,val)

I'd like to retrieve all payment emissions, with all relative part 
payments....(with a left join), for all ID_cli
and I'd like to calculate the rest to pay...like tot-SUM(val).
but I don't know how to obtain the subtotal of all the part payments 
reative to each payment,
so to calculate the remaining payment

this is my real query:

SELECT T.ID ID_tes, importo, tipo_doc, tot_doc,
tot_doc-SUM(importo) saldo,SUM(importo)
FROM mag_movtes T
LEFT JOIN mov_cas C ON T.ID=C.ID_tes AND verso='E' AND C.deleted=0
WHERE T.rif_CF='C' AND T.deleted=0 AND T.ID_CF=426
GROUP BY T.ID,C.ID
HAVING tot_doc-SUM(importo)!=0

these are the tables:


TABLE `mag_movtes`
   `ID` int(11) unsigned NOT NULL auto_increment,
   `data_mov` date default NULL,
   `rif_CF` set('C','F') default NULL,
   `ID_CF` int(11) unsigned default NULL,
   `ID_cau` tinyint(3) unsigned default NULL,
   `data_doc` date default NULL,
   `num_doc` tinytext,
   `tipo_doc` set('B','F','C','I','E') default NULL,
   `imp_doc` decimal(6,2) default NULL,
   `sconto` decimal(4,2) default NULL,
   `tot_doc` decimal(6,2) default NULL,
   `deleted` tinyint(1) unsigned NOT NULL default '0',

TABLE `mov_cas`
   `ID` int(10) unsigned NOT NULL auto_increment,
   `data` date NOT NULL default '0000-00-00',
   `rif_CF` enum('C','F') NOT NULL default 'C',
   `ID_CF` int(10) unsigned NOT NULL default '0',
   `ID_tes` int(10) unsigned NOT NULL default '0',
   `descr` tinytext,
   `importo` decimal(6,2) default NULL,
   `verso` enum('E','U') NOT NULL default 'E',
   `deleted` tinyint(1) NOT NULL default '0',








Thread
group by problemWakan19 Apr