the number has been doubled RRS feed

  • Question

  • User157772347 posted


    my query is ok without any error and it gets me what i want BUT it doubles the results!!!

    SELECT SUM(URSYSTEM.PolicyTable.OriginalPremium) AS expr1, SUM(URSYSTEM.Payments.Amount) AS expr2, LoginDB.[ahsal-UR].Users.Share
              URSYSTEM.PolicyTable CROSS JOIN
    WHERE  (URSYSTEM.PolicyTable.Producer = 'ahsal') AND (URSYSTEM.PolicyTable.Status = 'Active')
    GROUP BY LoginDB.[ahsal-UR].Users.Share

    the output now is

    Expr1 Expr2 Share
    120 30 50

    but it must be:

    Expr1 Expr2 Share
    60 15 50

    the Share is OK... but why Expr1 and Expr2 has been doubled ??!!

    Sunday, December 10, 2017 9:11 PM

All replies

  • User991499041 posted

    Hi Ahmedsalahaddin,

    It's not SUM() that's causing the doubling, it's the JOIN.

    SUM() is done after you're done your JOIN, and your JOIN appears to be matching one record from one table to two records from the other table.



    Monday, December 11, 2017 1:55 AM
  • User364663285 posted


    Please check if any detail record is leading to duplication of records.

    Tuesday, December 12, 2017 7:50 AM
  • User347430248 posted

    Hi ahmedsalahad...,

    looks like your join is bringing the more then one row cause this issue.

    i can see that you had already applied the GROUP BY.

    you can try to add multiple columns in GROUP BY.

    so that you can get proper output.

    further you can try to share your table design with dummy data.

    we will try to create dummy tables and try to test the query posted by you.

    we will try to check and correct it to get desired output.



    Wednesday, December 13, 2017 8:00 AM
  • User753101303 posted


    CROSS JOIN matches all rows of one table to all rows of the other table. This is likely not what you want here. Start with a basic query to make the joins you really want (I assume a user have policies and each Policy have payments ?) and once the result is ok add the group by stuff.

    Wednesday, December 20, 2017 9:29 AM