Re: Query help

On Tue, Jan 1, 2019 at 11:06 AM Chuck Martin <clmartin(at)theombudsman(dot)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
>