Steve, Exactly, the lcreceive has multiple records that match an id in lcopen. I use mysqldump to create an enclosed file - wood.sql for your reference. The database name of my file is "wood", it contains 2 tables "lcopen" and "lcreceive". "lcopen" contains the letter of credits (L/C) I have opened, and the description of the L/C. And "lcreceive" contains the Bill of Lading (B/L), the amount I have received. Any records in lcreceive that the due date before today are already solved and paid. I want to summarize the amount have not been solved, and sort by bank (OTB, IBA).
-- MySQL dump 9.07 -- -- Host: localhost Database: wood --------------------------------------------------------- -- Server version 4.0.7-gamma-nt -- -- Table structure for table 'lcopen' -- CREATE TABLE lcopen ( id int(10) unsigned NOT NULL auto_increment, bank varchar(4) default NULL, lc_number varchar(15) default NULL, description varchar(50) NOT NULL default '', unit_price_us decimal(8,2) NOT NULL default '0.00', order_cbm decimal(8,4) NOT NULL default '0.0000', PRIMARY KEY (id) ) TYPE=MyISAM; -- -- Dumping data for table 'lcopen' -- INSERT INTO lcopen VALUES (1,'OTB','196010294507','LAPACHO 20x130x600/750/900/1050/1200MM',775.00,60.0000); INSERT INTO lcopen VALUES (2,'OTB','196010294507','LAPACHO 20x100x600/750/900/1050/1200MM',735.00,40.0000); INSERT INTO lcopen VALUES (3,'OTB','196010297586','LAPACHO 20x130x600/750/900/1200MM',775.00,120.0000); INSERT INTO lcopen VALUES (4,'IBA','LC02000124','INCIENSO 20x80x450/600/750/900MM',670.00,21.0000); INSERT INTO lcopen VALUES (5,'IBA','LC02000124','LAPACHO 20x100x300/375/450MM',450.00,42.0000); INSERT INTO lcopen VALUES (6,'IBA','LC02000124','LAPACHO 20x130x600/750/900/1200MM',775.00,80.0000); INSERT INTO lcopen VALUES (7,'IBA','LC02000124','LAPACHO 20x100x600/750/900/1200MM',735.00,20.0000); INSERT INTO lcopen VALUES (8,'IBA','LC02000225','INCIENSO 20x130x600/750/900/1200MM',1100.00,20.0000); INSERT INTO lcopen VALUES (9,'IBA','LC02000225','INCIENSO 20x100x600/750/900/1200MM',1050.00,40.0000); INSERT INTO lcopen VALUES (10,'IBA','LC02000244','LAPACHO 20x100x600/750/900/1200MM',735.00,20.0000); INSERT INTO lcopen VALUES (11,'IBA','LC02000244','LAPACHO 20x130x600/750/900/1200MM',775.00,20.0000); INSERT INTO lcopen VALUES (12,'IBA','LC02000244','LAPACHO 20x80x450/600/750/900/1200MM',470.00,20.0000); INSERT INTO lcopen VALUES (13,'IBA','LC02000244','LAPACHO 20x100x300/375/450MM',450.00,20.0000); INSERT INTO lcopen VALUES (14,'OTB','196010322246','CUMARU 20x130x600/750/900/1200MM',640.00,60.0000); INSERT INTO lcopen VALUES (15,'OTB','196010322246','GUATAMBU 20x100x600/750/900/1200MM',530.00,40.0000); INSERT INTO lcopen VALUES (16,'OTB','196010333467','CURUPAY 20x130x600/750/900/1200MM',650.00,40.0000); INSERT INTO lcopen VALUES (17,'OTB','196010333467','CURUPAY 20x100x600/750/900/1200MM',610.00,20.0000); INSERT INTO lcopen VALUES (18,'OTB','196010333467','GUATAMBU 20x100x600/750MM',530.00,20.0000); INSERT INTO lcopen VALUES (19,'OTB','196010334457','LAPACHO 20x130x600/750/900/1200MM',775.00,10.9540); INSERT INTO lcopen VALUES (20,'OTB','196010334457','LAPACHO 20x100x600/750/900/1200MM',735.00,10.4490); INSERT INTO lcopen VALUES (21,'IBA','LC02000518','INCIENSO 20X100X600-900MM',1080.00,44.0000); INSERT INTO lcopen VALUES (22,'IBA','LC02000518','INCIENSO 20X130X600-1200MM',1120.00,66.0000); INSERT INTO lcopen VALUES (23,'OTB','196010342545','RED INCIENSO 20x100x450-1200MM',1080.00,23.0000); INSERT INTO lcopen VALUES (24,'OTB','196010342545','RED INCIENSO 20x130x450-1200MM',1140.00,22.0000); INSERT INTO lcopen VALUES (25,'IBA','LC02000555','RED INCIENSO-KD 20x130x600/750/900MM',1100.00,60.0000); INSERT INTO lcopen VALUES (26,'IBA','LC02000555','RED INCIENSO-KD 20x100x600/750/900MM',1050.00,40.0000); INSERT INTO lcopen VALUES (27,'IBA','LC02000555','RED INCIENSO-KD 20x80x450/600/750/900MM',650.00,20.0000); INSERT INTO lcopen VALUES (28,'IBA','LC02000555','GUATAMBU-KD 20x100x600/750/900/1200MM',530.00,20.0000); INSERT INTO lcopen VALUES (29,'OTB','196010348424','RED INCIENSO KD.S4S 20x130x450-1200MM',1140.00,44.0000); INSERT INTO lcopen VALUES (30,'OTB','196010348424','RED INCIENSO KD.S4S 20x100x450-1200MM',1080.00,22.0000); INSERT INTO lcopen VALUES (31,'OTB','196010348424','RED INCIENSO KD.S4S 20x80x450-1200MM',703.00,22.0000); INSERT INTO lcopen VALUES (32,'OTB','196010352686','RED INCIENSO KD.S4S 20x130x600-900MM',1140.00,45.0000); INSERT INTO lcopen VALUES (33,'OTB','196010352686','RED INCIENSO KD.S4S 20x100x450-900MM',1080.00,46.5000); INSERT INTO lcopen VALUES (34,'OTB','196010352686','RED INCIENSO AD.S4S 20x130x600-900MM',1039.00,19.0000); INSERT INTO lcopen VALUES (35,'OTB','196010352686','RED INCIENSO AD.S4S 20x100x600-900MM',968.00,19.0000); INSERT INTO lcopen VALUES (36,'IBA','LC02000702','GRAPIA 20x100x600/750/900/1200MM',680.00,42.6050); INSERT INTO lcopen VALUES (37,'IBA','LC02000702','CURUPAY 20x130x600/750/900/1200MM',580.00,63.0000); INSERT INTO lcopen VALUES (38,'IBA','LC02000702','RED INCIENSO 20x130x600/750/900/1200MM',1100.00,42.0000); INSERT INTO lcopen VALUES (39,'IBA','LC02000726','INCIENSO WOOD S4S 20X100X600-900MM',1090.00,66.0000); INSERT INTO lcopen VALUES (40,'IBA','LC02000726','INCIENSO WOOD S4S 20X130X600-1200MM',1140.00,44.0000); INSERT INTO lcopen VALUES (41,'OTB','196010363273','RED INCIENSO 20x130x300MM',500.00,22.0000); INSERT INTO lcopen VALUES (42,'OTB','196010363273','RED INCIENSO 20x100x450MM',700.00,13.2000); INSERT INTO lcopen VALUES (43,'OTB','196010363273','RED INCIENSO 20x100x300MM',500.00,8.8000); INSERT INTO lcopen VALUES (44,'OTB','196010363273','RED INCIENSO 20x130x600-900MM',1140.00,22.0000); INSERT INTO lcopen VALUES (45,'OTB','196010363273','RED INCIENSO 20x80x600-900MM',700.00,22.0000); INSERT INTO lcopen VALUES (46,'OTB','196010363273','GUATAMBU 20x100x450-1200MM',530.00,22.0000); INSERT INTO lcopen VALUES (47,'IBA','LC02000823','LAPACHO 20x100/130x450MM',450.00,22.0000); INSERT INTO lcopen VALUES (48,'IBA','LC02000823','LAPACHO 20x80x750/900/1200MM',450.00,22.0000); INSERT INTO lcopen VALUES (49,'IBA','LC02000823','GUATAMBU 20x100x600/750/900/1200MM',530.00,44.0000); INSERT INTO lcopen VALUES (50,'IBA','LC02000823','RED INCIENSO 20x130x600/750/900/1200MM',1100.00,22.0000); INSERT INTO lcopen VALUES (51,'OTB','196010370210','LAPACHO 19x130x600/750/900/1200MM',680.00,44.0000); INSERT INTO lcopen VALUES (52,'OTB','196010370210','RED INCIENSO 19x80x450/600/750/900/1200MM',650.00,21.0000); INSERT INTO lcopen VALUES (53,'OTB','196010370210','LAPACHO 19x130x600/750/900/1200MM',680.00,22.0000); INSERT INTO lcopen VALUES (54,'OTB','196010370210','LAPACHO 19x100x600/750/900/1200MM',630.00,22.0000); INSERT INTO lcopen VALUES (55,'OTB','196010370210','LAPACHO 20x100/130x300MM',350.00,22.0000); INSERT INTO lcopen VALUES (57,'OTB','196010372307','LAPACHO 19x100x600/750/900/1200MM',610.00,21.0000); INSERT INTO lcopen VALUES (56,'OTB','196010372307','LAPACHO 19x130x600/750/900/1200MM',680.00,63.0000); INSERT INTO lcopen VALUES (58,'OTB','196010372307','LAPACHO 19x130/100x300MM',350.00,21.0000); INSERT INTO lcopen VALUES (59,'OTB','196010372307','GUATAMBU 19x100x600/750/900/1200MM',530.00,42.0000); INSERT INTO lcopen VALUES (60,'OTB','196010374118','CUMARU 20x130x600/750/900MM',630.00,100.0000); INSERT INTO lcopen VALUES (61,'OTB','196010374118','JATOBA 19x120x600/750/900MM',700.00,80.0000); INSERT INTO lcopen VALUES (62,'IBA','LC03000037','LAPACHO 19x130x600/750/900/1200MM',680.00,63.0000); INSERT INTO lcopen VALUES (63,'IBA','LC03000037','LAPACHO 19x100x600/750/900/1200MM',610.00,42.0000); INSERT INTO lcopen VALUES (64,'IBA','LC03000037','LAPACHO 19x80x450/600/750/900/1200MM',430.00,21.0000); INSERT INTO lcopen VALUES (65,'IBA','LC03000037','LAPACHO 19x100/130x450MM',430.00,21.0000); INSERT INTO lcopen VALUES (66,'IBA','LC03000037','LAPACHO 19x100/130x300/375/450MM',400.00,20.0000); -- -- Table structure for table 'lcreceive' -- CREATE TABLE lcreceive ( id int(10) unsigned NOT NULL default '0', receive_cbm decimal(8,4) NOT NULL default '0.0000', bill varchar(20) default NULL, amount_us decimal(9,2) NOT NULL default '0.00', due_date date NOT NULL default '0000-00-00' ) TYPE=MyISAM; -- -- Dumping data for table 'lcreceive' -- INSERT INTO lcreceive VALUES (1,20.9820,'COSU84403940',16261.05,'2002-04-10'); INSERT INTO lcreceive VALUES (1,21.1970,'COSU84403710',16427.68,'2002-04-10'); INSERT INTO lcreceive VALUES (1,19.9330,'COSU84404000',15448.08,'2002-04-16'); INSERT INTO lcreceive VALUES (2,20.9720,'COSU84403960',15414.42,'2002-04-16'); INSERT INTO lcreceive VALUES (2,21.4960,'COSU84404000',15799.56,'2002-04-16'); INSERT INTO lcreceive VALUES (3,19.8850,'COSU84404040',15410.88,'2002-04-22'); INSERT INTO lcreceive VALUES (3,40.9810,'COSU84404080',31760.28,'2002-04-29'); INSERT INTO lcreceive VALUES (3,41.3760,'COSU84404500',32066.40,'2002-05-08'); INSERT INTO lcreceive VALUES (3,20.4040,'COSU84404580',15813.10,'2002-05-08'); INSERT INTO lcreceive VALUES (4,19.8100,'COSU84403950',13272.70,'2002-04-17'); INSERT INTO lcreceive VALUES (5,19.4430,'EISU370200001892',8749.35,'2002-06-10'); INSERT INTO lcreceive VALUES (5,19.8080,'1UEVL002662',8913.60,'2002-06-13'); INSERT INTO lcreceive VALUES (6,60.5730,'1UEVL002205',46944.08,'2002-06-03'); INSERT INTO lcreceive VALUES (6,19.5070,'1UEVL002321',15117.93,'2002-06-08'); INSERT INTO lcreceive VALUES (7,20.6440,'COSU84404640',15173.34,'2002-05-15'); INSERT INTO lcreceive VALUES (8,18.2450,'740027013',20069.50,'2002-10-16'); INSERT INTO lcreceive VALUES (9,18.6110,'740027059',19541.55,'2002-10-28'); INSERT INTO lcreceive VALUES (9,17.6740,'740027140',18557.70,'2002-11-05'); INSERT INTO lcreceive VALUES (10,20.6620,'EISU370200001990',15186.57,'2002-06-10'); INSERT INTO lcreceive VALUES (11,20.3810,'EISU370200001990',15795.28,'2002-06-10'); INSERT INTO lcreceive VALUES (13,19.7310,'EISU370200002007',8878.95,'2002-06-10'); INSERT INTO lcreceive VALUES (14,29.4370,'MOLU740027134',18839.68,'2002-10-28'); INSERT INTO lcreceive VALUES (14,31.9470,'MOLU740027111',20446.08,'2002-10-28'); INSERT INTO lcreceive VALUES (15,20.9580,'ASKH-0003B',11107.74,'2002-07-10'); INSERT INTO lcreceive VALUES (15,21.1520,'ASU-HKG/200207',11210.56,'2002-08-07'); INSERT INTO lcreceive VALUES (16,20.4980,'ASUHKG074',13323.70,'2002-09-23'); INSERT INTO lcreceive VALUES (16,20.5010,'ASUHKG073',13325.65,'2002-09-23'); INSERT INTO lcreceive VALUES (17,20.5000,'ASUHKG070',12505.00,'2002-09-23'); INSERT INTO lcreceive VALUES (18,21.7380,'ASU-HKG/200208',11521.14,'2002-09-23'); INSERT INTO lcreceive VALUES (19,10.9540,'COSU84406170',8489.35,'2002-09-30'); INSERT INTO lcreceive VALUES (20,10.4490,'COSU84406170',7680.02,'2002-09-30'); INSERT INTO lcreceive VALUES (21,48.4000,'ASHK-0004',52272.00,'2002-12-03'); INSERT INTO lcreceive VALUES (22,24.4930,'ASHK-0002A',27432.16,'2002-10-29'); INSERT INTO lcreceive VALUES (22,23.6130,'PGHK-2502',26446.56,'2002-10-29'); INSERT INTO lcreceive VALUES (22,24.4940,'ASHK-0007',27433.28,'2002-10-29'); INSERT INTO lcreceive VALUES (23,22.9674,'TPE158944-(06)',24804.79,'2002-11-11'); INSERT INTO lcreceive VALUES (24,21.6442,'TPE158944-(06)',24674.39,'2002-11-11'); INSERT INTO lcreceive VALUES (25,19.1210,'740027353',21033.10,'2002-11-06'); INSERT INTO lcreceive VALUES (25,19.9400,'740027451',21934.00,'2002-11-14'); INSERT INTO lcreceive VALUES (25,23.7060,'COSU84411280',26076.60,'2003-01-02'); INSERT INTO lcreceive VALUES (26,10.2030,'MOLU780008626',10713.15,'2003-01-15'); INSERT INTO lcreceive VALUES (26,9.4320,'740027756',9903.60,'2002-12-04'); INSERT INTO lcreceive VALUES (27,21.0400,'740027762',13676.00,'2002-12-04'); INSERT INTO lcreceive VALUES (28,20.5040,'ASKH-0003',10867.12,'2002-09-10'); INSERT INTO lcreceive VALUES (29,22.5202,'TPE163050-(02)',25673.03,'2002-12-23'); INSERT INTO lcreceive VALUES (29,22.4750,'PONLASU00409413',25621.50,'2002-12-18'); INSERT INTO lcreceive VALUES (30,23.3980,'PONLASU00409413',25269.84,'2002-12-18'); INSERT INTO lcreceive VALUES (31,22.3220,'PONLASU00409414',15692.37,'2002-12-23'); INSERT INTO lcreceive VALUES (32,45.0738,'ASU-HKG/200218',51384.13,'2003-01-02'); INSERT INTO lcreceive VALUES (33,46.8816,'ASU-HKG/200218',50632.13,'2003-01-02'); INSERT INTO lcreceive VALUES (34,18.1444,'PONLASU00409415',18852.03,'2003-01-02'); INSERT INTO lcreceive VALUES (35,18.1536,'PONLASU00409415',17572.68,'2003-01-02'); INSERT INTO lcreceive VALUES (36,42.6070,'KHHKT-1208',28972.76,'2003-01-06'); INSERT INTO lcreceive VALUES (37,20.0110,'COSU84411140',11606.38,'2003-01-06'); INSERT INTO lcreceive VALUES (37,20.0080,'MOLU780008684',11604.64,'2003-01-28'); INSERT INTO lcreceive VALUES (38,21.9310,'MOLU780008632',24124.10,'2003-01-15'); INSERT INTO lcreceive VALUES (39,24.2000,'ASHK-0002',26378.00,'2003-01-21'); INSERT INTO lcreceive VALUES (39,48.4000,'ASHK-0005B',52756.00,'2003-01-21'); INSERT INTO lcreceive VALUES (40,48.4000,'ASHK-0005B',55176.00,'2003-01-21'); INSERT INTO lcreceive VALUES (44,22.5498,'NYKS472800388',25706.77,'2003-02-19'); INSERT INTO lcreceive VALUES (45,22.1741,'PONLASU00409452',15521.87,'2003-02-24'); INSERT INTO lcreceive VALUES (46,23.1000,'PONLASU00409458',12243.00,'2003-03-12'); INSERT INTO lcreceive VALUES (48,22.4690,'COSU84412590',10111.05,'2003-02-04'); INSERT INTO lcreceive VALUES (42,13.4244,'MPA003129',9397.08,'2003-03-25'); INSERT INTO lcreceive VALUES (43,8.6040,'MPA003129',4302.00,'2003-03-25'); INSERT INTO lcreceive VALUES (41,20.6887,'MPA003128',10344.35,'2003-03-25'); INSERT INTO lcreceive VALUES (49,21.2830,'COSU84413750',11279.99,'2003-03-05'); INSERT INTO lcreceive VALUES (52,21.3100,'COSU84413690',13851.50,'2003-03-04'); INSERT INTO lcreceive VALUES (53,42.7090,'COSU84413690',29042.12,'2003-03-04'); INSERT INTO lcreceive VALUES (47,21.8550,'COSU84413700',9834.75,'2003-03-04'); INSERT INTO lcreceive VALUES (49,20.9130,'COSU84413710',11083.89,'2003-03-04'); INSERT INTO lcreceive VALUES (51,21.6540,'COSU84413670',14724.72,'2003-03-05'); INSERT INTO lcreceive VALUES (53,42.7090,'COSU84413690',29042.12,'2003-03-04'); INSERT INTO lcreceive VALUES (54,22.0430,'COSU84413730',13887.09,'2003-03-05'); Best regards, Terence Ng --- [EMAIL PROTECTED] wrote: > Hello, > > Yes I think I see what you are saying.If there are > multiple records > in table lcreceive that match an id in Icopen then > SUM(unit_price_us * > order_cbm) > is summing multiples of the same values and is > therefore wrong. > It should only be summing 1 row of values.It may be > difficult to solve > your problem with 1 SELECT.But we can try. > > Perhaps you can provide some create table and sample > data (inserts) and the result you want. > > Best, > steve > > In a message dated 1/22/2003 5:45:20 AM Eastern > Standard Time, > [EMAIL PROTECTED] writes: > > > > First of all, thanks for your help. > > > > I have discovered that the result from: > > > > SELECT > > bank, > > SUM(unit_price_us * order_cbm) > > FROM lcopen > > GROUP BY bank; > > > > is different to the query you provided (row OPEN). > > > How can I solve it? > > > > Terence Ng > > > > --- [EMAIL PROTECTED] wrote: > Hello Terence, > > > > > > I posted this to: > > > Newsgroups: > > > > HREF="http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&; > > > group=mailing.database.mysql">mailing.database.mysql > > > Date: 2003-01-08 01:16:04 PST > > > Subject: Question - Query > > > > > > Perhaps you didn't see it:). > > > > > > Use a CASE statement to conditionally SUM the > > > amount_us values, > > > ie. SUM only when lcreceive.due_date < > current_date > > > is true. > > > If false a null will be summed which SUM > excludes. > > > > > > SELECT > > > lcopen.bank, > > > SUM(lcopen.unit_price_us*lcopen.order_cbm) AS > > > open, > > > SUM(CASE WHEN lcreceive.due_date < current_date > > > THEN lcreceive.amount_us END) AS newamount > > > FROM lcopen, lcreceive > > > WHERE lcopen.id=lcreceive.id > > > GROUP BY lcopen.bank; > > > > > > You could also use IF instead of CASE. > > > > > > But there could be a problem with both > SUM's.MySql > > > returns a 0 when all values of expr in SUM(expr) > are > > > null instead of a null value.This behavior is > > > different from Min,Max and Avg which all return > null > > > and could lead to misleading > > > results/interpretations. > > > A workaround for returning a null instead of 0 > for > > > SUM > > > in the case of all null values is to first check > for > > > all nulls using an aggregate that will return a > null > > > in > > > this case.First check for a null using an > > > appropriate > > > aggregate then compute the sum or return > null.Here > > > MAX is used to check for null in both SUM > > > expressions. > > > > > > SELECT > > > lcopen.bank, > > > CASE WHEN > MAX(lcopen.unit_price_us*lcopen.order_cbm) > > > IS NOT NULL > > > THEN > SUM(lcopen.unit_price_us*lcopen.order_cbm) > > > ELSE NULL END * 7.8 AS > > > open, > > > CASE WHEN MAX(CASE WHEN lcreceive.due_date < > > > current_date > > > THEN lcreceive.amount_us END) IS NOT NULL > > > THEN SUM(CASE WHEN lcreceive.due_date < > > > current_date > > > THEN lcreceive.amount_us END) ELSE NULL END > AS > > > newamount, > > > FROM lcopen, lcreceive > > > WHERE lcopen.id=lcreceive.id > > > GROUP BY lcopen.bank; > > > > > > You could also use count(expr) for testing nulls > and > > > test for >0 > > > instead of IS NOT NULL. > > > You could also use IF instead of CASE.There are > many > > > different > > > ways of writing the logic. > > > > > > MySql hopefully will fix the SUM function in the > > > case of all nulls. > > > All enterprise rdbms return null in this case:). > > > > > > www.rac4sql.net > > > > > > > _______________________________________________________________________ Do You Yahoo!? Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk _______________________________________________________________________ Do You Yahoo!? Get your free @yahoo.com.hk address at http://mail.english.yahoo.com.hk --------------------------------------------------------------------- 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
