Re: Query help - Mailing list pgsql-general

On 1/1/19 11:26 AM, John W Higgins wrote:
> On Tue, Jan 1, 2019 at 11:06 AM Chuck Martin <clmartin@theombudsman.com 
> <mailto:clmartin@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)

Unfortunately there is a hitch in the above:(

select p_item_no from projection where sum(qty) > 100 group 
by(p_item_no); 

ERROR:  aggregate functions are not allowed in WHERE 
 

LINE 1: select p_item_no, sum(qty) from projection where sum(qty) >

> 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
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com