Results 1 to 6 of 6

Thread: sum of 3 tables

  1. #1
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132

    Unanswered: sum of 3 tables

    i have 3 tables:
    table1
    table2
    table3

    each table has total records and employee

    this is my query but it is still wrong.

    please advice


    SELECT
    coding_edit.`date`,
    coding_edit.`OP_#`,
    coder.Last_Name,
    SUM(coding_edit.Total_Batches_Edited) AS 'Total Batches Edited',
    SUM(coding_log.Total_Batches_coded) AS 'Total Batches coded',
    SUM(coding_exception.`Total_Batches(Exception)`) AS 'Total Batches excempted'
    FROM
    coding_edit
    INNER JOIN coding_log ON (coding_log.`Coder_OP#` = coding_edit.`OP_#`)
    INNER JOIN coder ON (coding_edit.`OP_#` = coder.`OP_#`)
    INNER JOIN coding_exception ON (coding_exception.`exception_by_OP_#` = coding_edit.`OP_#`)


    Group By
    coding_edit.`date`

    Order By
    coding_edit.`date` DESC
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what's wrong with it?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    all the sum is too big, my expected output is different from the output.
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by homer.favenir
    all the sum is too big, my expected output is different from the output.
    by what sort of factor, ie is it easy to identify what the discrepancy is.. understanding or identifying that is often the first step in resolving the problem

    are you using the right SQL aggregate term.. should it be count in place of sum

    should there be a where clause

    are your joins properly formed, should yo be using left (or right joins), is it

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem is the joins!!

    you did not describe your data, so i cannot figure out which joins are doing it, but you obviously have several one-to-many relationships, and the result is, you are getting one-to-too-many result rows

    which OP_#s are "one" and which are "many"? which are the foreign keys and which are the primary keys please?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Oct 2007
    Location
    Manila, Philippines
    Posts
    132
    all tables has Date, OP_# and Total Batches.
    my primary keyes are:
    idcoding_edit=(coding_edit.tbl)
    coID=(coding_log.tbl)
    idcoding_exception=(coding_exception.tbl)
    coderID=(coder.tbl)

    coder.tbl has only the lastname field.

    all tables, they all have the same fields.

    the output should like this.

    lastName/total batches coded/ total batches edited/batches excepted
    homer / 100 / 50 / 10

    but result of figure is too big and didnt match the actual total in the production.

    if you like, i have attached the sql of my database.

    thanks
    Attached Files Attached Files
    Take Nothing But Pictures;
    Leave Nothing But Footprints;
    Kill Nothing But Time;

Posting Permissions

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