CODEWITHSUNDEEP
postgresql
Deo Priya
Deo Priya

Reputation: 111

How can i use group by and order by together in postgresql

select t.spdi_application_id,t.spdi_attribute_id, t.spdi_attribute_value 
from schm_sp.spe_service_appl_cert_details t 
where spdi_attribute_id in(395,263,397,396,75) 
GROUP BY spdi_application_id 
ORDER BY spdi_application_id,spdi_attribute_id

i am using this command but i am getting error as follows

ERROR:  column "t.spdi_attribute_id" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: select t.spdi_application_id,t.spdi_attribute_id, t.spdi_att...
                                     ^

********** Error **********

ERROR: column "t.spdi_attribute_id" must appear in the GROUP BY clause or be used in an aggregate function
SQL state: 42803
Character: 30

How ever without group by it works fine with no errors.

Upvotes: 5

Views: 19818

Answers (3)

SomeWarez
SomeWarez

Reputation: 11

I know this is a very old thread, but I stumbled around the iNet and found this page while I was poking around and found the following to help me solve my issue:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY tGrp ORDER BY sNum) as RN
  FROM sSetup
) subquery
ORDER BY tGrp, RN;

Where sSetup is the name of the table, tGrp is an integer 0-1, and sNum is an integer 0-7. RN is a temp holder for count of each sNum in each tGrp. The subquery ROW_NUMBER() OVER (PARTITION BY part is what really helped me solve my issue of needing to do a group by and order by.

Upvotes: 1

Ajinkya Gurav
Ajinkya Gurav

Reputation: 9

I think you will need to use same columns in order by as those are in Group By. I also faced this issue and it got resolved by doing this. Therefore, the corrected SQL query for you is as below :

select t.spdi_application_id,t.spdi_attribute_id, t.spdi_attribute_value from schm_sp.spe_service_appl_cert_details t where spdi_attribute_id in(395,263,397,396,75) GROUP BY spdi_application_id ORDER BY spdi_application_id

Upvotes: 1

Don Zola
Don Zola

Reputation: 140

When you do a "group by" you are flattening down the rows by a column or columns. For the columns not in the "group by" you need to tell Postgres how it should merge a column's data using an aggregate function.

http://www.postgresql.org/docs/current/static/functions-aggregate.html

In your case you need to tell Postgres how to flatten the columns attribute_id and attribute_value when there are multiple rows with the same application_id. Should it return the max() id and value? Should it sum() them together? Should it concat them into an array of values? Should it return the average value?

select app_id, max(attr_id) as max_attr_id from foo group by app_id;

Upvotes: 4

Related Questions