Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2008
    Posts
    6

    Unanswered: Multiple SUM(Column) on different table at the same time

    I want to SUM(COLUMN) on different columns of different table at the same time- in a single query in MsAcces.And there is a single condition which every table should meet.I tried the following way-
    SELECT
    SUM(SalesInvoice.`TotalAmount`)AS SalesInvoice_TotalAmount,
    SUM(SalesInvoice.`Paid`)AS SalesInvoice_Paid,
    SUM(SalesInvoice.`Due`)AS SalesInvoice_Due,
    SUM(PurInvoice.`TotalAmount`)AS PurInvoice_TotalAmount,
    SUM(PurInvoice.`Paid`)AS PurInvoice_Paid,
    SUM(PurInvoice.`Due`)AS PurInvoice_Due,
    SUM(EmpPayment.`Payment`)AS EmpPayment_Payment,
    SUM(Cost.`Amount`)AS Cost_Amount,
    SUM(OutSideIncome.`Amount`)AS OutSideIncome_Amount,
    SUM(BankTransaction.`DipositAmount`)AS BankTransaction_DipositAmount,
    SUM(BankTransaction.`WithdrawnAmount`)AS BankTransaction_WithdrawnAmount,
    SUM(BankTransaction.`Balance`)AS BankTransaction_Balance
    FROM
    `SalesInvoice` SalesInvoice,
    `PurInvoice` PurInvoice,
    `EmpPayment` EmpPayment,
    `Cost` Cost,
    `OutSideIncome` OutSideIncome,
    `BankTransaction` BankTransaction

    WHERE SalesInvoice.SalesDate BETWEEN #1/1/2008# AND #6/30/2008#
    AND PurInvoice.PurchaseDate BETWEEN #1/1/2008# AND #6/30/2008#
    AND EmpPayment.Paymentdate BETWEEN #1/1/2008# AND #6/30/2008#
    AND Cost.Date BETWEEN #1/1/2008# AND #6/30/2008#
    AND OutSideIncome.Date BETWEEN #1/1/2008# AND #6/30/2008#
    AND BankTransaction.Date BETWEEN #1/1/2008# AND #6/30/2008#
    But it is not generating the accurate result.Is there any problem? PLS help.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by tanvirtonu
    But it is not generating the accurate result.
    I bet it isn't.

    You are CROSS JOINING your tables - this means that you are creating a CARTESIAN PRODUCT. What does all that mean? It means that you are joining EVERY row in EVERY table to EVERY row in ALL the other tables.
    You need to create some joins between your tables. How experienced are you at creating queries? I ask because joins are pretty fundamental but this query is not the sort of query I would expect someone to produce early on in their learning.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT 'SalesInvoice' AS source    
         , 'TotalAmount' AS sum_type
         , SUM(TotalAmount) AS sum_amount
      FROM SalesInvoice
     WHERE SalesDate BETWEEN #1/1/2008# AND #6/30/2008#
    UNION ALL
    SELECT 'SalesInvoice', 'Paid', SUM(Paid)
      FROM SalesInvoice
     WHERE SalesDate BETWEEN #1/1/2008# AND #6/30/2008#
    UNION ALL
    SELECT 'SalesInvoice', 'Due', SUM(Due)
      FROM SalesInvoice
     WHERE SalesDate BETWEEN #1/1/2008# AND #6/30/2008#
    UNION ALL
    SELECT 'PurInvoice', 'TotalAmount', SUM(TotalAmount)
      FROM PurInvoice
     WHERE PurchaseDate BETWEEN #1/1/2008# AND #6/30/2008#
    UNION ALL
    SELECT 'PurInvoice', 'Paid', SUM(Paid)
      FROM PurInvoice
     WHERE PurchaseDate BETWEEN #1/1/2008# AND #6/30/2008#
    UNION ALL
    SELECT 'PurInvoice', 'Due', SUM(Due)
      FROM PurInvoice
     WHERE PurchaseDate BETWEEN #1/1/2008# AND #6/30/2008#
    UNION ALL
    SELECT 'EmpPayment', 'Payment', SUM(Payment)
      FROM EmpPayment
     WHERE Paymentdate BETWEEN #1/1/2008# AND #6/30/2008#
    UNION ALL
    SELECT 'Cost', 'Amount', SUM(Amount)
      FROM Cost
     WHERE Date BETWEEN #1/1/2008# AND #6/30/2008#
    UNION ALL
    SELECT 'OutSideIncome', 'Amount', SUM(Amount)
      FROM OutSideIncome
     WHERE Date BETWEEN #1/1/2008# AND #6/30/2008#
    UNION ALL
    SELECT 'BankTransaction', 'DipositAmount', SUM(DipositAmount)
      FROM BankTransaction
     WHERE Date BETWEEN #1/1/2008# AND #6/30/2008#
    UNION ALL
    SELECT 'BankTransaction', 'WithdrawnAmount', SUM(WithdrawnAmount)
      FROM BankTransaction
     WHERE Date BETWEEN #1/1/2008# AND #6/30/2008#
    UNION ALL
    SELECT 'BankTransaction', 'Balance', SUM(Balance)
      FROM BankTransaction
     WHERE Date BETWEEN #1/1/2008# AND #6/30/2008#
    by the way, the `backticks` are MySQL only
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937
    by the way, the `backticks` are MySQL only
    OMG - there you go again
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah I notice a significant increase in R2d2's references to MySQL too
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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