RE: Query help - Mailing list pgsql-general
| From | Scot Kreienkamp |
|---|---|
| Subject | RE: Query help |
| Date | |
| Msg-id | 17082AAFC33A934082836458CB53494374E1A595@MONDB03.na.lzb.hq Whole thread Raw |
| In response to | Query help (Chuck Martin <clmartin@theombudsman.com>) |
| List | pgsql-general |
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.
pgsql-general by date: