trouble GROUPing rows in postgres

Question

I have a table myTable

id  | test_id_1 | test_id_2  
001 | 0         | 1  
001 | 1         | 0  
002 | 1         | 0  
002 | 0         | 1  
003 | 1         | 0  
003 | 0         | 1

And I'm trying to group the rows based on id so that I can get single rows with the id like such

 id | test_id_1 | test_id_2  
001 | 1         | 1    
002 | 1         | 1    
003 | 1         | 1

This is my query

SELECT *
FROM myTable
GROUP BY id

I keep getting an error saying that test_id_1 is not in the GROUP BY. But if I do include it I don't get the table I am looking for.

postgresql.org/docs/current/static/…

user330315
– user330315

2015-09-23 06:07:50 +00:00
Commented Sep 23, 2015 at 6:07 — user330315
– user330315, Commented Sep 23, 2015 at 6:07

Henry Liang · Accepted Answer · 2015-09-23 00:23:21Z

3

Try this (I assume what you need is the sum of test_id_1 and test_id_2):

SELECT id, SUM(TEST_ID_1), SUM(TEST_ID_2)
FROM myTable
GROUP BY id

The group by syntax requires you to aggregate all the columns are not listed in "group by" clause, and that is the reason you got error. By speaking of aggregation, you can use SUM, COUNT, MAX OR MIN functions.

answered Sep 23, 2015 at 0:23

Henry Liang

1371 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ABHISHEK RANA · Accepted Answer · 2015-09-23 06:35:40Z

0

You can also get the same output, while you go for maximum value of test_id_1 and test_id_2 for each id wise group. So you can try the below query also

SELECT id, MAX(TEST_ID_1), MAX(TEST_ID_2)
FROM myTable
GROUP BY id

answered Sep 23, 2015 at 6:35

ABHISHEK RANA

3273 silver badges8 bronze badges

Collectives™ on Stack Overflow

trouble GROUPing rows in postgres

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related