Re: Query help

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