DT
Drizzle Team•5mo ago
devlsh

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
mediumhype•5mo ago
Are you sure that getTableColumns doesn't also include song_history.id?
Angelelz
Angelelz•5mo ago
Not just that, any column other that the one in the aggreagate function need to be in the group by clause
Mario564
Mario564•5mo ago
Usuully, just adding the PK column works, in that case it would be adding songHistory.id to group by

Did you find this page helpful?