From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | John W Higgins <wishdev(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Query help |
Date: | 2019-01-01 19:44:03 |
Message-ID: | 073395bd-912c-2e4e-7892-58aa86283c52@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(at)theombudsman(dot)com
> <mailto: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)
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2019-01-01 20:05:41 | Re: Query help |
Previous Message | Adrian Klaver | 2019-01-01 19:40:22 | Re: Query help |