Re: Query help

[John W Higgins <wishdev@xxxxxxxxx>]

On Tue, Jan 1, 2019 at 11:06 AM Chuck Martin <clmartin@xxxxxxxxxxxxxxxx> 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