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