By looking at your query and table description, I guess you can try something like:
SELECT cus.`Code` AS `Customer Code`, cus.`Name` AS `Customer`,SUM((1-bil.direction)*bil.Total) AS Loan, SUM(bil.direction*bil.Total) AS Credit FROM customer AS cus, bill AS bil WHERE bil.`Customer` = cus.`ID` GROUP BY bil.`Customer` ORDER BY cus.`Name` Adolfo On Wed, 2003-01-01 at 14:41, Aziz Durmaz wrote: > Hi, > > I have got 2 tables one of the is "customers" the other is "bill". In the > customers table i store my customers informations. In the bill table i store > my billings. if i take a bill bill.direction=0, if i give a bill > bill.direction=1. And now i must take a report Like This.. > > Customer Code Customer loan > credit total > ---------------------------------------------------------------------------- > --------------------- > 10021 Bob 2515$ > 500$ 2015$ > 10022 Mark 530$ > 600$ -70$ > 10023 Fred 7500$ > 0 7500$ > .......... > and its goes on... > > Bill Table > ----------- > CREATE TABLE `bill` ( > `ID` int(11) NOT NULL auto_increment, > `direction` tinyint(4) NOT NULL default '0', > `BillNo` varchar(16) NOT NULL default '', > `BillDate` date default '0000-00-00', > `Customer` int(11) default '0', > `Total` float(10,2) default '0.00' > PRIMARY KEY (`ID`), > UNIQUE KEY `ID` (`ID`), > UNIQUE KEY `BillNo` (`BillNo`), > KEY `BillDate` (`BillDate`), > KEY `Customer` (`Customer`) > ) TYPE=MyISAM; > > Customer Table > --------------- > > CREATE TABLE `customer` ( > `ID` int(10) unsigned NOT NULL auto_increment, > `Code` varchar(32) NOT NULL default '0', > `Name` varchar(80) default NULL, > PRIMARY KEY (`ID`), > UNIQUE KEY `Code` (`Code`), > KEY `Name` (`Name`) > ) TYPE=MyISAM; > > > İ use a query like > ------------------ > SELECT cus.`Code` AS `Customer Code`, > cus.`Name` AS `Customer`, > IF (bil.`direction`=0,SUM(bil.`Total`), NULL ) AS Loan, > IF (bil.`direction`=1,SUM(bil.`Total`), NULL ) AS Credit > FROM customer AS cus, > bill AS bil > > WHERE bil.`Customer` = cus.`ID` > GROUP BY bil.`Customer` > ORDER BY cus.`Name` > > > But it gives wrong results. My query is to add all Totals, its doesnt look > if direction is 1 or 0. The out put like this > > Customer Code Customer loan > credit > ---------------------------------------------------------------------------- > -------- > 10021 Bob 3015$ > 0 > 10022 Mark 0 > 1030$ > 10023 Fred 7500$ > 0 > .......... > > > > I must send only one query ! Is there any body can help me??? > > > > > Aziz Durmaz > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
