How to select several columns in Postgres without group by all columns

Question

I'm using Postgres 8.4.2 and I'm trying to make a query that selects some data but I get wrong results when group by these several columns I select.

I tried not to group by all of these columns, but I get notice, that I should group by all selected columns.

My query looks like that:

 SELECT c.id AS category_id, c.parent_id, c.title AS category_title, count(ofrs.id) AS offers_count
   FROM categories c
   LEFT JOIN offers_to_categories otc ON c.id = otc.category_id
   LEFT JOIN offers ofrs ON otc.offer_id = ofrs.id
  WHERE ofrs.offer_type = 1 AND ofrs.status = 1
  GROUP BY c.id, c.title, c.parent_id;

I want to select offers count by category where offer_type = 1.

How could I do that without group by several columns but only group by c.id?

I tried the following window function but result is the same - it shows me more results than it should be.

SELECT  ofr.id , c.id AS category_id, c.parent_id, c.title AS category_title,ofr.website_id ,
 count( ofr.id) 
 OVER (PARTITION BY  (ofr.id) order BY ofr.id)
 FROM offers AS ofr
 INNER JOIN offers_to_categories AS ofr_cat ON (ofr_cat.offer_id = ofr.id)
 INNER JOIN categories AS c ON (c.id = ofr_cat.category_id)
 WHERE (c.id = 3 or c.parent_id = 3) and ofr.website_id = 1 and ofr.status = 1

Check out window functions. And you should really plan an update to a supported version. 8.4 is extremely old. You should at least upgrade to the latest 8.4 version — user330315
– user330315, Commented Jan 27, 2016 at 19:43
I'm reading now about them - postgresql.org/docs/8.4/static/tutorial-window.html . But I can't understand how to use it to group by ofr.id only. I edited my question. — ivva
– ivva, Commented Jan 27, 2016 at 19:50
Please edit your question and add some sample data and the expected output — user330315
– user330315, Commented Jan 27, 2016 at 20:03
Since it looks like all the values in the columns are going to be identical you would normally use a dummy aggregate like min() or max() to pull out the values you need. — shawnt00
– shawnt00, Commented Jan 27, 2016 at 20:17
Why should I use min(), max() aggregate functions, I need to select these columns ..I found that this query is working most of the time, but in one category_id it returns 2 more results and I can't find why. — ivva
– ivva, Commented Jan 27, 2016 at 20:29

Praveen Kumar Purushothaman · Accepted Answer · 2016-01-27 21:01:42Z

2

I found answer for my question and it is:

SELECT  distinct ON(ofr.id) ofr.id , c.id AS category_id, c.parent_id, c.title AS category_title,ofr.website_id ,
count( ofr.id) 
 OVER (PARTITION BY  (select distinct on(ofr.id) ofr.id from offers as id GROUP BY ofr.id) order BY ofr.id)
FROM offers AS ofr
INNER JOIN offers_to_categories AS ofr_cat ON (ofr_cat.offer_id = ofr.id)
INNER JOIN categories AS c ON (c.id = ofr_cat.category_id)
WHERE (c.id = 3 or c.parent_id = 3) and ofr.website_id = 1 and ofr.status = 1

edited Jan 27, 2016 at 21:01

Praveen Kumar Purushothaman

167k27 gold badges215 silver badges261 bronze badges

answered Jan 27, 2016 at 20:14

ivva

2,9699 gold badges33 silver badges65 bronze badges

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

Collectives™ on Stack Overflow

How to select several columns in Postgres without group by all columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related