Thread: Query help
Any columns that aren’t involved in a summary operation (think math type or some other type of summary operation) have to be in the group by statement.
From what you show below, I would try something like this (untested):
Select accountid,name,sum(amount) from table where sum(amount) >’50’ group by accountid,name sort by accountid,name;
You can’t show the transaction ID unless you have duplicate transaction ID’s that you wanted to group by. If you did try to show it you’d get the entire table. Or you could use a more advanced query to gather the multiple transaction ID’s into a single record for the query results which would let the sum and group by work.
Scot Kreienkamp |Senior Systems Engineer | La-Z-Boy Corporate
One La-Z-Boy Drive| Monroe, Michigan 48162 | Office: 734-384-6403 | | Mobile: 7349151444 | Email: Scot.Kreienkamp@la-z-boy.com
From: Chuck Martin [mailto:clmartin@theombudsman.com]
Sent: Tuesday, January 01, 2019 2:06 PM
To: pgsql-general <pgsql-general@postgresql.org>
Subject: Query help
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
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
This message is intended only for the individual or entity to which it is addressed. It may contain privileged, confidential information which is exempt from disclosure under applicable laws. If you are not the intended recipient, you are strictly prohibited from disseminating or distributing this information (other than to the intended recipient) or copying this information. If you have received this communication in error, please notify us immediately by e-mail or by telephone at the above number. Thank you.
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. name1 bill2. james3 sarah4 carltransactionid. amount. accountid. name1. 50. 1 bill2. 25. 2 james3 35 4 carl4. 75. 1 bill5 25. 1 bill6 50 3 sarahresults wanted-all transactions where account total >= 50id. amount. accountid. name1. 50. 1 bill3. 75. 1 bill4 25. 1 bill5 50 3 sarah
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 MartinAvondale Software
results wanted-all transactions where account total >= 50id. amount. accountid. name1. 50. 1 bill3. 75. 1 bill4 25. 1 bill5 50 3 sarah
On Tuesday, January 1, 2019, Chuck Martin <clmartin@theombudsman.com> wrote:results wanted-all transactions where account total >= 50id. amount. accountid. name1. 50. 1 bill3. 75. 1 bill4 25. 1 bill5 50 3 sarahThis result does not require group by, just the where clause you’ve noted above.
On 1/1/19 11:05 AM, Chuck Martin 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 > > 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. Window Functions?: https://www.postgresql.org/docs/11/tutorial-window.html Or do something like(untested): select transactionid, amount, accountid, name from transaction join (select accountid, sum(amount) from transaction group by(accountid)) as account_sum on transaction.transactionid = account_sum.accountid and account_sum.sum >= 50 > > Chuck Martin > Avondale Software -- Adrian Klaver adrian.klaver@aklaver.com
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
On 1/1/19 11:26 AM, John W Higgins wrote: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
On 1/1/19 12:05 PM, David G. Johnston wrote: > On Tuesday, January 1, 2019, Adrian Klaver <adrian.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.com>> wrote: > > On 1/1/19 11:26 AM, John W Higgins wrote: > > 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 > > > Which is where the HAVING clause comes in. It filters out groups based > on an expression containing an aggregate function. Hmm, guess I should spend more time reading the SELECT docs:) > > David J. -- Adrian Klaver adrian.klaver@aklaver.com