AnsweredAssumed Answered

SQL MULTIPLING MY COUNT HELP!!

Question asked by WilliamSouth on Sep 19, 2014

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!

Outcomes