Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2012
    Posts
    2

    Unanswered: Join tables with additional Sum Query

    Hello all
    I have the following SQL statement to fill a datagrid from a mdb:

    Code:
    SELECT        modProducts.Product_Name, SysProductCategories.Product_Category, SysProductSubCategories.Product_SubCategory, modProducts.ProductDescription, 
                             modContacts.Company_Name, modProducts.VendorCode, modProducts.ProductPrice, modProducts.ProductCost, modProducts.ProductProfit, modProducts.StockLimit,
                              SysMetrics.MetricsValue
    FROM            ((((modProducts LEFT OUTER JOIN
                             modContacts ON modProducts.ProductVendor = modContacts.RecordID) LEFT OUTER JOIN
                             SysProductSubCategories ON modProducts.ProductSubCategory = SysProductSubCategories.RecordID) LEFT OUTER JOIN
                             SysProductCategories ON modProducts.ProductCategory = SysProductCategories.RecordID) LEFT OUTER JOIN
                             SysMetrics ON modProducts.MetricUnit = SysMetrics.RecordID)
    ORDER BY modProducts.Product_Name
    To get the sum column I'm searching for, I have to use this SQL:
    Code:
    SELECT        SUM(modTransaction_Items.Quantity) AS TotalSales
    FROM            (modProducts LEFT OUTER JOIN
                             modTransaction_Items ON modProducts.RecordID = modTransaction_Items.Product_ID)
    HAVING        (modTransaction_Items.InvoiceType = 1)
    Another sum is this:
    Code:
    SELECT        SUM(modTransaction_Items.Quantity) AS TotalBuys
    FROM            (modProducts LEFT OUTER JOIN
                             modTransaction_Items ON modProducts.RecordID = modTransaction_Items.Product_ID)
    HAVING        (modTransaction_Items.InvoiceType = 2)
    and finally I would like to have the difference between the 2 sums: totalbuys-totalsales

    How can all of these have place in one and only statement? How can I fix the first SQL query to include the rest 3 sums?

    Thank you.
    Last edited by ktab; 01-17-12 at 10:08.

  2. #2
    Join Date
    Jan 2012
    Posts
    2
    any ideas?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •