SUM in WHERE clause

I am having some trouble with a query. Using MySQL 5.

2 tables. HEDGE, HEDGE_CLOSED

The hedge table keeps track of opening hedge positions and the hedge_closed table keeps track of the closing hedge positions on a hedge contract number.

With this query, I am trying to find the hedge_contract_numbers of hedges in the hedge table that have not been closed out in the hedge_closed table.

I need to compare hedge.hedge_contract_count to make sure it’s not equal to SUM(hedge_closed.hedge_closed_contracts). If they are equal, that would mean the positions are closed. I am only looking for open positions.
Code Snippet:

1:
2:
3:
4:
5:
6:
7:

SELECT DISTINCT(hedge.hedge_contract_number) as hedge_contract_number
FROM hedge LEFT JOIN hedge_closed ON hedge.hedge_contract_number = hedge_closed.hedge_closed_hedge_number
WHERE hedge.hedge_contract_count != SUM(hedge_closed.hedge_closed_contracts)
GROUP BY hedge_contract_number

SELECT h.hedge_contract_number, SUM(h.hedge_contract_count) as h_count,
SUM(IF(hedge_closed_contracts IS NULL,0,hedge_closed_contracts )) as hc_count
FROM hedge h
LEFT JOIN hedge_closed hc
ON h.hedge_contract_number = hc.hedge_closed_hedge_number
GROUP BY h.hedge_contract_number, h.lot_id
HAVING h_count hc_count
Not what you’re looking for? Ask an Expert.

SELECT x.hedge_contract_number, SUM(x.hedge_contract_count) as h_count,
SUM(IF(x.hedge_closed_contracts IS NULL,0,x.hedge_closed_contracts )) as hc_count
FROM
(SELECT DISTINCT h.hedge_contract_number, h.hedge_contract_count, hedge_closed_contracts
FROM hedge h
LEFT JOIN hedge_closed hc
ON h.hedge_contract_number = hc.hedge_closed_hedge_number ) x
GROUP BY x.hedge_contract_number
HAVING h_count hc_count