Select results of query and search those records on original table

Question

I have the following table1:

      id       date    desc  amount
12345678 2020-09-11 payment 4999.15
12345678 2020-09-11     tax -548.20
12345678 2020-09-11   total 4450.95
12345678 2020-08-11 payment 4999.15
12345678 2020-08-11     tax -548.20
12345678 2020-08-11   total 4450.95
98765432 2020-09-11 payment   99.15
98765432 2020-09-11     tax  -99.15
98765432 2020-09-11   total    0.00
98765432 2020-08-11 payment  499.00
98765432 2020-08-11     tax -299.00
98765432 2020-08-11   total  200.00

I want to select the IDs that earn less than 250 total. I am applying the following query:

SELECT id, ROUND(SUM(amount), 2) AS 'TOTAL EARNED'
FROM table1
WHERE desc = 'total' 
GROUP BY id
HAVING ROUND(SUM(amount), 2) < 250
ORDER BY ROUND(SUM(amount), 2);

It works well and I get back a table with two columns: one for the ID and one for the total earned. Now, I'd like to get all the data associated to those IDs from table1, except the rows where desc = 'total'. In this example, the result would be this:

      id       date    desc  amount
98765432 2020-09-11 payment   99.15
98765432 2020-09-11     tax  -99.15
98765432 2020-08-11 payment  499.00
98765432 2020-08-11     tax -299.00

I have tried subqueries (SELECT * FROM (SELECT id, ...) AS T;), but they all look inside the results table and not table1, therefore I don't get the desired output.

How can I get this result?

Answer 1

You can use window functions:

select *
from (
    select t.*,
        sum(case when descr = 'total' then amount else 0 end)
            over(partition by id) total_earned
    from table1 t
) t
where descr <> 'total' and total_earned < 250

In the subquery, the window sums the amounts associated to description 'total' for each id. This basically emulates the behavior of your original query, without aggregation. Then, the outer query filters on the total earned and on the description.

Note that desc is a language keyword (as in order by clauses), hence not a good choice for a column name. I renamed it to descr in the query.