1

I'm trying to do the following with postgres:

  • count distinct
  • table joins
  • group by hstore key

I don't think I'm too far, but the count distinct isn't added up per group by values.

Here is the code on rextester.com

What I have so far:

SELECT COUNT(DISTINCT pets.id),locations.attr -> 'country' as country
FROM pets,photos,locations
WHERE photos.pet_id = pets.id
AND photos.location_id = locations.id
GROUP BY pets.id,locations.attr -> 'country';

Which gives me:

enter image description here

Whereas I want:

enter image description here

Max
  • 12,794
  • 30
  • 90
  • 142

1 Answers1

1

Lose the pets.id from GROUP BY:

SELECT COUNT(DISTINCT pets.id),locations.attr -> 'country' as country
FROM pets,photos,locations
WHERE photos.pet_id = pets.id
AND photos.location_id = locations.id
GROUP BY locations.attr -> 'country';

EDIT:

You don't really need to join the pets table. Also, use explicit JOIN syntax:

select
    l.attr -> 'country' country,
    count(distinct p.pet_id)
from photos p
inner join locations l
on p.location_id = l.id
group by l.attr -> 'country';

Without using COUNT(DISTINCT):

select 
    country, count (pet_id)
from (
    select
        l.attr -> 'country' country,
        p.pet_id
    from photos p
    inner join locations l
    on p.location_id = l.id
    group by l.attr -> 'country', p.pet_id
) t
group by country;

http://rextester.com/YVR16306

Gurwinder Singh
  • 38,557
  • 6
  • 51
  • 76
  • Indeed (tired). Since this was an easy one, would you happen to know how to adapt the query to use the more efficient `SELECT COUNT(*) FROM (SELECT DISTINCT` instead of `SELECT COUNT(DISTINCT`? http://stackoverflow.com/questions/11250253/postgresql-countdistinct-very-slow#answer-14732410 – Max Jan 17 '17 at 16:43
  • Beautiful! if I may ask, what's the added value of using the explicit `JOIN` syntax versus what I was doing? – Max Jan 17 '17 at 17:02
  • The explicit join is modern and more clear and easy to understand whereas the other syntax is 20 years old. – Gurwinder Singh Jan 17 '17 at 17:03