Struggling with a GROUP BY count

with this query,

const data = await db
  .select({
    ...getTableColumns(songHistory),
    count: sql<number>`cast(count(${songHistory.id}) as int)`,
  })
  .from(songHistory)
  .where(and(eq(songHistory.user_id, user!).if(typeof user === 'number'), gte(songHistory.played_at, from!).if(!!from)))
  .groupBy(songHistory.song_title)
  .orderBy(desc(songHistory.played_at));

const data = await db
  .select({
    ...getTableColumns(songHistory),
    count: sql<number>`cast(count(${songHistory.id}) as int)`,
  })
  .from(songHistory)
  .where(and(eq(songHistory.user_id, user!).if(typeof user === 'number'), gte(songHistory.played_at, from!).if(!!from)))
  .groupBy(songHistory.song_title)
  .orderBy(desc(songHistory.played_at));

i'm getting the error,

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

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

which i'm a bit confused on? i'm using the id col is in the aggregate function already

3 Replies

mediumhype•2w ago

Are you sure that getTableColumns doesn't also include song_history.id?

Angelelz•2d ago

Not just that, any column other that the one in the aggreagate function need to be in the group by clause

Mario564•2d ago

Usuully, just adding the PK column works, in that case it would be adding songHistory.id to group by

Gaming

Programming

Struggling with a GROUP BY count