New topic: OpenBase not grouping
<http://forums.realsoftware.com/viewtopic.php?t=30475> Page 1 of 1 [ 1 post ] Previous topic | Next topic Author Message Miguel Dina Post subject: OpenBase not groupingPosted: Wed Oct 14, 2009 8:21 am Joined: Wed Mar 26, 2008 7:09 pm Posts: 79 Hi to all, I believe this is my mistake but since i just can't find the error for what seams to be a very simple SQL query your help is very much appreciated. Tables Req - Parts requisition Inv - Inventory data What i need >From the query you can tell i want a list of items grouped by FabPN with the >quantity total (Cant) for each. Title of items (Inv.ItemTitle) will be taken >from the Inv table by matching the FabPN field. The Query "SELECT Req.FabPN, Inv.ItemTitle, Sum(Req.Cant), Inv.CostoFOB, f_New FROM Req, Inv WHERE Inv.FabPN = Req.FabPN GROUP BY Req.FabPN" The result FabPN ItemTitle Cant CostoFOB f_New ------------------------------------------------------------------------------------------ 0199-004 AXIS 206 Network Camera 18 200.99000000 1 0199-004 AXIS 206 Network Camera 18 200.99000000 0199-004 AXIS 206 Network Camera 18 200.99000000 0199-004 AXIS 206 Network Camera 18 200.99000000 0199-024 AXIS 206 Surveillance Bundle 64 0.000000000 0199-024 AXIS 206 Surveillance Bundle 64 0.000000000 1 0199-024 AXIS 206 Surveillance Bundle 64 0.000000000 0199-024 AXIS 206 Surveillance Bundle 64 0.000000000 0199-044 AXIS 206 10 Pack 8 0.000000000 1 0199-044 AXIS 206 10 Pack 8 0.000000000 0220-004 AXIS 213 PTZ Network Camera 15 0.000000000 0246-004 AXIS 214 PTZ Network Camera 71 0.000000000 1 0299-001 AXIS P3343-VE 6mm 50 1008.99000000 1 0299-001 AXIS P3343-VE 6mm 50 1008.99000000 0299-001 AXIS P3343-VE 6mm 50 1008.99000000 0299-001 AXIS P3343-VE 6mm 50 1008.99000000 0299-001 AXIS P3343-VE 6mm 50 1008.99000000 0299-031 AXIS P3343-VE 12mm 8 1008.99000000 21774 "Smoke colored dome, indoor for 231D/232D" 10 28.28000000 5005-001 "Casing for AXIS 216FD/216MFD/P3301, smoked" 3 29.29000000 5005-011 "Casing for AXIS 216FD/216MFD/P3301, clear" 1 29.29000000 5005-041 "Drop ceiling mount for AXIS 216FD/216MFD/P3301, s 4 59.59000000 665544 Cable UTP Cat 8E 2 0.000000000 666 HELL ITEM 66 0.000000000 77411 66666666666 12 0.000000000 876 Ochocientos Setenta y Seis 3 0.000000000 9988 Risa deshidratadas 24 0.000000000 1 ------------------------------------------------------------------------------------------ Problem All items occurrences are listed instead of a single entry for each one. Interestingly the 'Cant' value represents the sum of quantity for each item. That is ok. Some ideas Using "Distinct FabPN' will do the trick but could i also get the quantity total (Cant) by FabPN?. Which is the best way to built this query?. Why all items occurrences are been listed if the GROUP BY statement is in place?. Thanks in advance, Miguel Dina J. Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 1 post ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
