CODEWITHSUNDEEP
sqlpostgresqlgroup-by
aubonphysics
aubonphysics

Reputation: 25

trouble GROUPing rows in postgres

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.

Upvotes: 0

Views: 34

Answers (2)

ABHISHEK RANA
ABHISHEK RANA

Reputation: 327

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

Upvotes: 0

Henry Liang
Henry Liang

Reputation: 137

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.

Upvotes: 3

Related Questions