On Tue, Jan 1, 2019 at 11:06 AM Chuck Martin <clmar...@theombudsman.com> 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 > > You have 2 concepts here - identify the accounts with a total over 50 and then show the transactions for those accounts. I prefer CTEs here because they allow for better understanding (to me) of the steps involved. A subquery would work here as well. with accounts_over_total as ( select accountid from transactions where sum(amount) >= 50 group by accountid) select transactions.* from transactions join accounts_over_total on transactions.accountid = accounts.accountid John > 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. > > Chuck Martin > Avondale Software >
