On 1/1/19 11:05 AM, Chuck Martin wrote:
> Sorry if this is too basic a question for this list, but I don't fully
> get how to use aggregates (sum()) and group-by together. I'm trying to
> get a list of transactions where the total for a given account exceeds a
> given number. I'm not sure an example is needed, but if so, consider
> this simplified data:
>
> accountid. name
> 1 bill
> 2. james
> 3 sarah
> 4 carl
>
> transaction
> id. amount. accountid. name
> 1. 50. 1 bill
> 2. 25. 2 james
> 3 35 4 carl
> 4. 75. 1 bill
> 5 25. 1 bill
> 6 50 3 sarah
>
> results wanted-all transactions where account total >= 50
>
> id. amount. accountid. name
> 1. 50. 1 bill
> 3. 75. 1 bill
> 4 25. 1 bill
> 5 50 3 sarah
>
> I've tried to understand how to use GROUP BY and HAVING, but the penny
> won't drop. I keep getting errors saying that all columns in the SELECT
> have to also be in the GROUP BY, but nothing I've done seems to produce
> the correct results. I think because the GROUP BY contains multiple
> columns, so each row is treated as a group. It also is difficult to
> parse out since in the real world, many more tables and columns are
> involved.
Window Functions?:
https://www.postgresql.org/docs/11/tutorial-window.html
Or do something like(untested):
select transactionid, amount, accountid, name from transaction join
(select accountid, sum(amount) from transaction group by(accountid)) as
account_sum on transaction.transactionid = account_sum.accountid and
account_sum.sum >= 50
>
> Chuck Martin
> Avondale Software
--
Adrian Klaver
adrian.klaver@aklaver.com
