How can I get the IDs of the rows that make up a GROUP BY ... HAVING query?

Question

I have the following query that looks for transactions that cancel themselves out from the same customer (Some transactions are negative).

SELECT c, ABS(r) magnitude, SUM(r) total, COUNT(*) num
FROM table
GROUP BY c, magnitude
HAVING num > 1 AND total = 0
ORDER BY total

The result of this query is the customer id, the magnitude of the orders, the sum of the orders that cancel themselves out (zero), and the number of transactions that, together, add up to zero.

How would I go about getting the IDs of the rows that make up the COUNT(*)?

I'd like to end up with a single column result set that contains the IDs of those rows.

Answer 1

For some reason I thought the tags said MySQL. I'm pretty dumb.

---

You might want to consider the group_concat function, which will let you have a column which is all of the IDs in the group concatenated together in a list.

It wouldn't really be useful for anything other than a human reading the list. If you want to do more queries or calculations based on the result then it's not a good option.

But basically you could do something like this:

SELECT c, ABS(r) magnitude, SUM(r) total, COUNT(*) num,
  GROUP_CONCAT(ID SEPARATOR ', ') id_list
FROM table
GROUP BY c, magnitude
HAVING num > 1 AND total = 0
ORDER BY total

Answer 2

SELECT  id
FROM    (
        SELECT  c, ABS(r) magnitude, SUM(r) total, COUNT(*) num
        FROM    table
        GROUP BY
                c, magnitude
        HAVING num > 1 AND total = 0
        ) td
JOIN    table to
ON      to.c = td.c
        AND to.r IN (magnitude, -magnitude)

Creating an index on (c, r) will improve this query.

In SQL Server, Oracle and PostgreSQL 8.4 you can also do the following:

SELECT  id
FROM    (
        SELECT  id,
                SUM(r) OVER (PARTITION BY c, ABS(r)) total,
                COUNT(*) OVER (PARTITION BY c, ABS(r)) num
        FROM    table
        ) q
WHERE   num > 1 AND total = 0