0 Replies Latest reply on Sep 19, 2014 9:02 AM by WilliamSouth

    SQL MULTIPLING MY COUNT HELP!!

    WilliamSouth

      Title

      SQL MULTIPLING MY COUNT HELP!!

      Post

      Im trying to make a sql statement that tells me when certain orders are completed and paid for then they can be ordered and confirmed.

      I came up with this statement that does what I need except for one error(it Multiplies the COUNT of how many records I have on order.

      SELECT COUNT(a."PARTY ID MATCH"), b."No BM", b."PARTY ID", b."FirstName", b."LastName", b."Wear Date"
      FROM "Customers" a
      INNER JOIN "Registration" b ON a."PARTY ID MATCH" = b."PARTY ID"
      LEFT JOIN "Purchase Orders PO" c ON b."PARTY ID" = c."kf_party_id"
      WHERE b."No BM" > 0 AND a."Role" = BRIDESMAID AND   c."OrderStatus" = No Confomation Yet
      GROUP BY a."PARTY ID MATCH", b."No BM", b."PARTY ID", b."FirstName", b."LastName", b."Wear Date"
      HAVING COUNT(a."PARTY ID MATCH") >= b."No BM"

      COUNT(a."PARTY ID MATCH") is what is multiplied once grouped.

      Here is an Example of the Results

      COUNT(a."PARTY ID MATCH"),  |    b."No BM"  |  b."PARTY ID"    |b."FirstName"| b."LastName"|   b."Wear Date"

                          6                                |      4              |       473               |      BILL         |      SOUTH    |   2014-11-15

       I only have 2 Records entered and the Count is 6,  The only theory I could see this happening is because I have 3 different tables.

      So I Tried another Sql Statement which gives me the right number because it only has 2 Tables, But once I have confirmed my order it won't eliminate or omit the results it will just keep populating more results. Because I don't have those 3rd Table calculations.

      here is that example

      SELECT COUNT(a."PARTY ID MATCH"), b."No BM", b."PARTY ID", b."FirstName", b."LastName", b."Wear Date"
      FROM "Customers" a
      INNER JOIN "Registration" b ON a."PARTY ID MATCH" = b."PARTY ID"
      WHERE b."No BM" > ? AND a."Role" = ?
      GROUP BY a."PARTY ID MATCH", b."No BM", b."PARTY ID", b."FirstName", b."LastName", b."Wear Date"
      HAVING COUNT(a."PARTY ID MATCH") >= b."No BM"

      COUNT(a."PARTY ID MATCH"),  |    b."No BM"  |  b."PARTY ID"    |b."FirstName"| b."LastName"|   b."Wear Date"

                             2                            |             2        |          463             |   John          |   Doe             |      2014-09-13
                             3                            |             3        |          464             |   Jane          |   Smith          |      2014-09-27
                             1                            |             1        |          470             |   BILL           |   BRADY       |      2014-12-06

      This is Great but can't omit any results :(

      Please I have been racking my brain for the last week working on this any help would be awesome!