SUM() not working as expected

[charles kline]

Hi all,

Just trying to figure out why the SUM() in this query is not returning 
the sum, but is returning the same value that signed_amt contains. 
Anyone have a clue? Thanks for any help :)

Here are the results I am getting:

id | signed_amt | balance_amt | entry_dtm
---+------------+-------------+-----------
 7 |        -20 |         -20 | 1078117200
 7 |          3 |           3 | 1078263566
Here is the query:

SELECT u.id,
  CASE
    WHEN tt.trans_cd =  'D' THEN trans_amt *  - 1
    WHEN tt.trans_cd =  'C' THEN trans_amt
  END AS signed_amt,
SUM
(
  CASE
    WHEN tt.trans_cd =  'D' THEN trans_amt *  - 1
    WHEN tt.trans_cd =  'C' THEN trans_amt
  END
) AS balance_amt, t.entry_dtm
FROM clients u, tbl_transactions t, tbl_transaction_types tt
WHERE t.client_id = u.id AND t.trans_id = tt.trans_type_id
GROUP  BY u.id, t.entry_dtm, signed_amt
Here is some data to play with:

CREATE TABLE `clients` (
  `id` int(11) NOT NULL auto_increment,
  `company` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=8 ;
INSERT INTO `clients` VALUES (1, 'client_1');
INSERT INTO `clients` VALUES (7, 'test client');
CREATE TABLE `tbl_transaction_types` (
  `trans_type_id` int(11) NOT NULL auto_increment,
  `trans_name` varchar(10) NOT NULL default '',
  `trans_cd` char(1) NOT NULL default '',
  PRIMARY KEY  (`trans_type_id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
INSERT INTO `tbl_transaction_types` VALUES (1, 'Debit', 'D');
INSERT INTO `tbl_transaction_types` VALUES (2, 'Credit', 'C');
CREATE TABLE `tbl_transactions` (
  `trans_id` int(11) NOT NULL auto_increment,
  `client_id` int(11) NOT NULL default '0',
  `trans_type_id` double NOT NULL default '0',
  `trans_amt` double NOT NULL default '0',
  `entry_dtm` int(11) NOT NULL default '0',
  PRIMARY KEY  (`trans_id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
INSERT INTO `tbl_transactions` VALUES (1, 7, '2', '20', 1078117200);
INSERT INTO `tbl_transactions` VALUES (2, 7, '1', '3', 1078263566);


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]