knex SQL - column must appear in the GROUP BY clause or be used in an aggregate function

Question

I am using postgres and knex with NodeJS to aggregate session_ids by hours in a given date range. My start_timestamp is in format2016-12-12 14:53:17.243-05. I want to group all records by the hour such that:

Hour 14:00:00-15:00:00 would have n records within that hour.
Hour 15:00:00-16:00:00 would have n records within that hour.
etc...

Given the query

db.knex.count('t1.id as session_ids')
    .from('sessions as t1')
    .where(db.knex.raw('t1.start_timestamp'), '>=', startDate)
    .andWhere(db.knex.raw('t1.start_timestamp'), '<=', endDate)
    .groupByRaw("date_trunc('hour', t1.start_timestamp)");

My start date and end date dictate a range of 1 Day. So there shouldn't be duplicate/ambiguous times based on grouping by hour of the day.

I am succesfully able to get counts of each record by the hour:

[ anonymous { session_ids: '6' },
  anonymous { session_ids: '1' },
  anonymous { session_ids: '1' },
  anonymous { session_ids: '3' },
  ...

But I need their actual time displayed, as such:

{
    hour: 10
    session_ids: 5 //5 session IDs 
}

Adding .select('t1.start_timestamp') below count, as shown in this example, I get the following error:

Unhandled rejection error: column "t1.start_timestamp" must appear in the GROUP BY clause or be used in an aggregate function

This error doesn't make sense to me as t1.start_timestamp appears in the GROUP BY stage.

Moreover, I've already checked out PostgreSQL -must appear in the GROUP BY clause or be used in an aggregate function for help. I don't have ambiguity in my records so the DB should know which records to select.

I am facing the same issue, do you resolved it? – Khalifa Gad Sep 05 '22 at 10:34 — Khalifa Gad, Sep 05 '22 at 10:34
was able to solve it by adding select statement – Khalifa Gad Sep 05 '22 at 10:38 — Khalifa Gad, Sep 05 '22 at 10:38

score 0 · Answer 1 · answered Dec 13 '16 at 04:33

I am not familiar with knex, but the following query does not work (it gives the same error message you noted):

SELECT t1.start_timestamp, count(t1.id) AS session_ids
FROM sessions AS t1
GROUP BY date_trunc('hour', t1.start_timestamp);

However, the following query does work:

SELECT date_trunc('hour', t1.start_timestamp), count(t1.id) AS session_ids
FROM sessions AS t1
GROUP BY date_trunc('hour', t1.start_timestamp);

Can you try changing .select accordingly?

knex SQL - column must appear in the GROUP BY clause or be used in an aggregate function

1 Answers1