Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 2 of 2 total |
is it possible to make this query? |
Thu, Aug 9 2007 5:25 AM | Permanent Link |
"Santy Concepción" | Hi!
I have two tables ('sell' and 'buy') that contains the products I have sold or bought: TABLE SELL: PRODUCT QUANTITY 0001 2 TABLE BUY: PRODUCT QUANTITY 0001 1 I need to show the SUM of sold units and the SUM of bought units of each product, but I always get wrong values. This is what I try: select SUM(sell.quantity) as sold, SUM(buy.quantity) as bought from sell, buy where sell.PRODUCT = buy.PRODUCT And this is what I get: RESULT: sold bought 2 2 ?? I have tried to group by product, delete the 'where' clause, etc... but the result is always the same. How can I get the real sum of each table? |
Thu, Aug 9 2007 10:30 AM | Permanent Link |
"Robert" | "Santy Concepción" <santyweb@hotmail.com> wrote in message news:03CC380B-4F3F-4D16-A801-8856BBDB19D4@news.elevatesoft.com... > Hi! > > I have two tables ('sell' and 'buy') that contains the products I have > sold or bought: > > TABLE SELL: > PRODUCT QUANTITY > 0001 2 > > TABLE BUY: > PRODUCT QUANTITY > 0001 1 > > I need to show the SUM of sold units and the SUM of bought units of each > product, but I always get wrong values. > > > This is what I try: > > select SUM(sell.quantity) as sold, SUM(buy.quantity) as bought from > sell, buy where sell.PRODUCT = buy.PRODUCT > > > And this is what I get: > > RESULT: > sold bought > 2 2 ?? > > I have tried to group by product, delete the 'where' clause, etc... but > the result is always the same. > How can I get the real sum of each table? > It is a bit complicated, especially if you need to account for unmatched items on either side (a product with sales but no purchases, or viceversa). I would combine the two tables into one before doing the aggregation, as follows: SELECT PROD PROD, QTY PURCHASES, 0 SALES INTO MEMORY\TEMP FROM BUY UNION ALL SELECT PROD, 0, QTY FROM SELL; SELECT PROD, SUM(PURCHASES) PURCHASES, SUM(SALES) SALES FROM MEMORY\TEMP GROUP BY PROD; Make sure you use UNION ALL, otherwise you will lose identical purchases or sales for the same product. Robert |
This web page was last updated on Monday, May 6, 2024 at 03:23 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |