locked
the number has been doubled RRS feed

  • Question

  • User157772347 posted

    hello,

    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
    FROM   URSYSTEM.Payments CROSS JOIN
              URSYSTEM.PolicyTable CROSS JOIN
              LoginDB.[ahsal-UR].Users
    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.

    Regards,

    zxj

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

    Hi,

    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.

    Regards

    Deepak

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

    Hi,

    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