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
mediumhype3mo ago
Are you sure that getTableColumns doesn't also include song_history.id?
Angelelz
Angelelz3mo ago
Not just that, any column other that the one in the aggreagate function need to be in the group by clause
Mario564
Mario5643mo ago
Usuully, just adding the PK column works, in that case it would be adding songHistory.id to group by
Want results from more Discord servers?
Add your server