SUM(DISTINCT x) as y

[Brooksie155]

I'm trying to get a calculation from my sql, which requires joining two tables in order to check the criteria of the rows. The problem is that the second table contains multiple rows and the SUM function is adding all the duplicates together rather than just once per unique row in table_a.

SELECT
SUM(a.net) AS net,
a.date
FROM
table_a a
INNER JOIN table_b ON a.id = b.id
WHERE
a.date = '2007-05-31'
AND b.counter > 0
GROUP BY a.dat_date _out
ORDER BY a.date DESC

I was hoping that the following would resolve the problem, but causes an error instead:

SUM(DISTINCT(a.net)) AS net

Any ideas? Cheers B.

[Brooksie155]

As usual you pull your hair out, ask for help and suddenly the answer pops out of knowhere:

SELECT
SUM(a.net) AS net,
a.date
FROM
table_a a
WHERE
a.date = '2007-05-31'
AND b.counter > 0
AND (SELECT b.counter FROM table_b b WHERE b.id = a.id ORDER BY b.counter DESC LIMIT 1) > 0
GROUP BY a.date
ORDER BY a.date DESC