1

I am selecting column used in group by and count, and query looks something like 

SELECT s.country, count(*) AS posts_ct
FROM   store          s
JOIN   store_post_map sp ON sp.store_id = s.id
GROUP  BY 1;

However, I want to select some more fields, like store name or store address from store table where count is max, but I don't to include that in group by clause.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
bhupesh
  • 25
  • 6
  • [Preceding, related question.](http://stackoverflow.com/questions/14871316/how-to-get-count-from-mapping-table-group-be-another-table-in-postgresql) – Erwin Brandstetter Feb 14 '13 at 11:33

1 Answers1

1

For instance, to get the stores with the highest post-count per country:

SELECT DISTINCT ON (s.country)
       s.country, s.store_id, s.name, sp.post_ct
FROM   store          s
JOIN  (
    SELECT store_id, count(*) AS post_ct
    FROM   store_post_map
    GROUP  BY store_id
    ) sp ON sp.store_id = s.id
ORDER  BY s.country, sp.post_ct DESC

Add any number of columns from store to the SELECT list.

Details about this query style in this related answer:

Reply to comment

This produces the count per country and picks (one of) the store(s) with the highest post-count:

SELECT DISTINCT ON (s.country)
       s.country, s.store_id, s.name
      ,sum(post_ct) OVER (PARTITION BY s.country) AS post_ct_for_country
FROM   store          s
JOIN  (
    SELECT store_id, count(*) AS post_ct
    FROM   store_post_map
    GROUP  BY store_id
    ) sp ON sp.store_id = s.id
ORDER  BY s.country, sp.post_ct DESC;

This works because the window function sum() is applied before DISTINCT ON per definition.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • This is returning store details with posts_ct of store having max posts in each group, however what I am looking for is posts_ct for sum of posts for group and details of store having max post in the group, hope this is clear now and apology for the confusion. – bhupesh Feb 14 '13 at 12:12
  • @bhupesh: How would you pick the "winning" store in a group, when multiple stores share the same highest count? Pick one randomly? Show multiple rows? – Erwin Brandstetter Feb 14 '13 at 12:35
  • yes, details of random store will work in my case, however posts_count must be sum from each store in group (city, country etc), can you please help with that? – bhupesh Feb 14 '13 at 12:52
  • @bhupesh: Added another solution. I guess we are getting there now? :) – Erwin Brandstetter Feb 14 '13 at 13:15