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.

On Tue, Jan 1, 2019 at 11:06 AM Chuck Martin <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)
select transactions.* from transactions join accounts_over_total on
transactions.accountid = accounts.accountid

John

On Tuesday, January 1, 2019, Chuck Martin <clmartin@theombudsman.com> wrote:

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

This result does not require group by, just the where clause you’ve noted
above.

David J.

On Tuesday, January 1, 2019, David G. Johnston <david.g.johnston@gmail.com>
wrote:

On Tuesday, January 1, 2019, Chuck Martin <clmartin@theombudsman.com>
wrote:

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

This result does not require group by, just the where clause you’ve noted
above.

Never mind...missed how the 25 got included

David J.

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 Tuesday, January 1, 2019, Adrian Klaver <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.

David J.

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