PostgreSQL Selecting and Grouping

Question

I'm newish to SQL and playing with a Pokemon database to learn. Starting to grasp some differences with MYSQL, SQLite and PostgreSQL.

SQLlite allows me to use the following query to grab Pokemon with only a single type (they have only one row using the Select statement as type_names.name will generate two rows--one for each type--if they have two types):

    -- displays all pokemon of a single type (no dual-types)
SELECT pokemon_species.id,
       pokemon_species.identifier,
       type_names.name
FROM pokemon_species
    JOIN pokemon_types ON pokemon_species.id = pokemon_types.pokemon_id
    JOIN type_names ON pokemon_types.type_id = type_names.type_id
GROUP BY 1
    HAVING COUNT(*) = 1
ORDER BY pokemon_species.id;

PostgreSQL, however, will give the "[42803] ERROR: column "type_names.name" must appear in the GROUP BY clause or be used in an aggregate function Position: 70" error.

I've found the following query works with PostgreSQL:

-- displays all pokemon of a single type (no dual-types)
WITH species AS (
    SELECT pokemon_species.id,
           pokemon_species.identifier
    FROM pokemon_species
             JOIN pokemon_types ON pokemon_species.id = pokemon_types.pokemon_id
             JOIN type_names ON pokemon_types.type_id = type_names.type_id
    GROUP BY 1, 2
             HAVING COUNT(*) = 1
    ORDER BY pokemon_species.id
)

SELECT species.*, type_names.name  
    FROM species
             JOIN pokemon_types ON species.id = pokemon_types.pokemon_id
             JOIN type_names ON pokemon_types.type_id = type_names.type_id;

Joining the three tables twice, seems redundant and I am wondering--how could this query be written better?

Result example:

id | identifier | type_name
-- | ---------- | ---------
4  | charmander | Fire
5  | charmeleon | Fire
7  | squirtle   | Water
8  | wartortle  | Water
9  | blastoise  | Water
10 | caterpie   | Bug
11 | metapod    | Bug

Gordon Linoff · Accepted Answer · 2020-07-23 12:28:42Z

1

You are looking for just one matching row in each group, so you can use an aggregation function:

SELECT ps.id, ps.identifier, MAX(tn.name) as name
FROM pokemon_species ps JOIN
     pokemon_types pt
     ON ps.id = pt.pokemon_id JOIN
     type_names tn
     ON pt.type_id = tn.type_id
GROUP BY 1
HAVING COUNT(*) = 1
ORDER BY ps.id;

Presumably, pokemon_species.id is declared as a primary key. That is why you can leave ps.identifier out of the GROUP BY.

answered Jul 23, 2020 at 12:28

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

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

1 Comment

user13587369 Over a year ago

Thanks for the help! Didn't think to throw in an aggregation that does nothing but satisfy the error message. I also didn't realize that ps.identifier didn't need to be part of the GROUP BY as ps.id is the primary key for that table.

Gabriel Durac · Accepted Answer · 2020-07-23 11:23:59Z

1

You can filter on the pokemon ids that are single type.

SELECT pokemon_species.id,
       pokemon_species.identifier,
       type_names.name
FROM pokemon_species
    JOIN pokemon_types ON pokemon_species.id = pokemon_types.pokemon_id
    JOIN type_names ON pokemon_types.type_id = type_names.type_id
WHERE pokemon_species.id IN (
   SELECT pokemon_id
   FROM pokemon_types
   GROUP BY pokemon_id 
   HAVING COUNT(*) = 1
)

Or you could use window functions to count the different types, and then filter

WITH CTE AS 
(SELECT pokemon_species.id,
       pokemon_species.identifier,
       type_names.name as type_name,
       COUNT(distinct type_names.name) OVER (PARTITION BY pokemon_species.id) as type_count
FROM pokemon_species
    JOIN pokemon_types ON pokemon_species.id = pokemon_types.pokemon_id
    JOIN type_names ON pokemon_types.type_id = type_names.type_id
)
SELECT id,
       identifier,
       type_name
FROM CTE
WHERE type_count=1

answered Jul 23, 2020 at 11:23

Gabriel Durac

2,7681 gold badge14 silver badges13 bronze badges

1 Comment

user13587369 Over a year ago

Thanks for the suggestions! I wanted to mention that the first option is a working solution and that I completely forgot about 'IN'. I read a bit about window functions in looking for a solution to this, but haven't really tried any yet. This one though throws a "[0A000] ERROR: DISTINCT is not implemented for window functions" error. Thanks again!

user330315 · Accepted Answer · 2020-07-23 11:29:26Z

0

Join after the aggregation:

SELECT ps.id,
       ps.identifier,
       tn.name
FROM (
  SELECT id,
         identifier,
  FROM pokemon_species 
  GROUP BY ps.id
  HAVING COUNT(*) = 1
) ps 
  JOIN pokemon_types pt ON ps.id = pt.pokemon_id
  JOIN type_names tn ON pt.type_id = tn.type_id
ORDER BY pokemon_species.id;

answered Jul 23, 2020 at 11:29

user330315

Collectives™ on Stack Overflow

PostgreSQL Selecting and Grouping

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related