SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2008
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    select and sum from two tables

    hi to all

    Ihave a main table with unique "id" with relation in table1 and table2 "main_id". I want to query and ouput the information from main table including the total of debitval from table1 and creditval from table2. Any help would greatly appreciated.

    Thanks in advance.

    maintable

    id payee description reference_no
    1 john advance 1
    2 dave request 2

    table1

    id main_id debitval
    1 1 5.00
    2 1 10.00
    3 2 6.00
    4 2 7.00

    table2

    id main_id creditval
    1 1 5.00
    2 1 10.00
    3 1 15.00
    4 2 4.00

    output table should be like this

    id payee description reference_no debitval creditval
    1 john advance 1 15.00 30.00
    2 dave request 2 13.00 4.00

  2. #2
    Follow Me On Twitter: @djg gold trophysilver trophybronze trophy Dan Grossman's Avatar
    Join Date
    Aug 2000
    Location
    Philadephia, PA
    Posts
    20,580
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    SELECT
      maintable.id,
      SUM(table1.debitval) AS `debitval`,
      SUM(table2.creditval) AS `creditval`
    FROM
      maintable
    LEFT OUTER JOIN
      table1
    ON 
      maintable.id = table1.main_id
    LEFT OUTER JOIN
      table2
    ON
      maintable.id = table2.main_id
    GROUP BY 
      maintable.id

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2008
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi Dan Grossman,

    Thank for your immediate replied. It work's like a charm.

    Best Regards,
    Tirso

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    sorry, dan, your query produces these results:
    Code:
    id  debitval  creditval
     1    45.0       60.0
     2    13.0        8.0
    your results are inflated because of cross join effects

    this query --
    Code:
    SELECT maintable.id
         , maintable.payee 
         , maintable.description 
         , maintable.reference_no
         , d.total_debit
         , c.total_credit
      FROM maintable
    LEFT OUTER 
      JOIN ( SELECT main_id
                  , SUM(debitval) AS total_debit
               FROM table1
             GROUP
                 BY main_id ) AS d
        ON d.main_id = maintable.id
    LEFT OUTER 
      JOIN ( SELECT main_id
                  , SUM(creditval) AS total_credit
               FROM table2
             GROUP
                 BY main_id ) AS c
        ON c.main_id = maintable.id
    produces the correct results --
    Code:
    id  payee  description  reference_no  total_debit  total_credit
     1  john   advance          1            15.0          30.0
     2  dave   request          2            13.0           4.0
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Dec 2008
    Posts
    47
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi r937,

    I'll notice a while a go that dan solution produce incorrect result. But somehow it was help me a lot.

    Thank you r937.

    Tirso


Bookmarks

Posting Permissions

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