Group by on non id field

Question

I have the following setup of tables:

CREATE TABLE public.tags (
tag_id int4 NOT NULL,
creation_timestamp timestamp NULL,
"name" varchar(255) NULL,
CONSTRAINT tags_pkey PRIMARY KEY (tag_id)
);


-- public.tag_targets definition

-- Drop table

-- DROP TABLE public.tag_targets;

CREATE TABLE public.tag_targets (
    id int4 NOT NULL,
    creation_timestamp timestamp NULL,
    target_id int8 NULL,
    target_name varchar(255) NULL,
    last_update_timestamp timestamp NULL,
    tag_id int4 NULL,
    CONSTRAINT tag_targets_pkey PRIMARY KEY (id),
    CONSTRAINT fkcesi55mqvysjv63c1xf2j15oh FOREIGN KEY (tag_id) REFERENCES tags(tag_id)
);

I am trying to run the following query:

SELECT *
FROM tag_targets tt, tags t 
WHERE tt.tag_id = t.tag_id
AND (t."name" IN ('Keeper', 'Pk'))
GROUP by tt.target_id

However it wants the PK of both Tags and Tagtarget in the group by:

 ERROR: column "tt.id" must appear in the GROUP BY clause or be used in an aggregate function

Is there anyway to group on the target_id column? Also feel free to give any feedback on table design as I went for a generic mapping table and independent tags table

why you wanna group by? show us your desired output – eshirvana May 24 '21 at 22:32 — eshirvana, May 24 '21 at 22:32

score 1 · Answer 1 · answered May 24 '21 at 22:31

The problem is that you are requesting SELECT * but in GROUP BY you specified only tt.target_id. Generally speaking All column names in SELECT list must appear in GROUP BY. Oversimplifying: your database doesn't know what to do with all values you requested in select, that weren't used in GROUP BY or any agregate.

Try running following query to see if you are getting something

SELECT tt.target_id, count(*)
FROM tag_targets tt, tags t 
WHERE tt.tag_id = t.tag_id
AND (t."name" IN ('Keeper', 'Pk'))
GROUP by tt.target_id

score 1 · Answer 2 · answered May 24 '21 at 23:39

Unrelated but your syntax of table1, table2 with the join in the "where" clause is the non-ANSI syntax. It's not wrong or anything, but the ANSI syntax of explicit joins is preferred for a litany of reasons I won't go into:

SELECT *
FROM
  tag_targets tt
  join tags t on
    tt.tag_id = t.tag_id
where
  t."name" IN ('Keeper', 'Pk')

On the surface, when you say group I am wondering if you mean "sort..." I am assuming you are new to SQL, so if that's an oversimplification, forgive me, but this would be perhaps what you wanted -- an "order by" instead of a group by.

SELECT *
FROM
  tag_targets tt
  join tags t on
    tt.tag_id = t.tag_id
where
  t."name" IN ('Keeper', 'Pk')
order by
  tt.target_id

If, on the other hand, you only wanted a single record for each target_id (which is truly a "group by target_id"), then perhaps this is what you wanted... one record per target_id, but then you have to identify how to prioritize which order is selected. In this example, I say pick the one based on the most recent updated date:

SELECT distinct on (tt.target_id)
  *
FROM
  tag_targets tt
  join tags t on
    tt.tag_id = t.tag_id
where
  t."name" IN ('Keeper', 'Pk')
order by
  tt.target_id, tt.last_update_timestamp desc

Not confident on either of these suggestions, so if they miss the mark, post some sample data and expected results.

Nitpicking: the implicit joins in the WHERE clause are "ANSI joins" - just the outdated ones from before '89 — , May 25 '21 at 05:26

Group by on non id field

2 Answers2