Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clau-CodePudding

I have a table offset_table in which the offset in minutes are written. This table will have exactly 1 record and 1 column.

So I need to add those number of minutes into the registration_time.

I have tried it using following approaches. But no luck.

Approach 1 :

    SELECT 
    DATEPART(week, CAST(DATEADD(MINUTE,(select offset from offset_table) , registration_time) as date)) as weeknumber
    FROM 
    registration
    GROUP BY
    DATEPART(week, CAST(DATEADD(MINUTE,(select offset from offset_table)  , registration_time) as date));

Error : Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

Approach 2 :

    SELECT 
    DATEPART(week, CAST(DATEADD(MINUTE,(select offset from offset_table) , registration_time) as date)) as weeknumber
    FROM 
    registration
    GROUP BY
    weeknumber;

Error : Invalid column name 'weeknumber'.

Approach 3 :

DECLARE @offsetInMin int;
SET @offsetInMin = (select offset from offset_table);

 SELECT 
    DATEPART(week, CAST(DATEADD(MINUTE,@offsetInMin , registration_time) as date)) as weeknumber
    FROM 
    registration
    GROUP BY
    DATEPART(week, CAST(DATEADD(MINUTE,@offsetInMin  , registration_time) as date));

Issue with Approach 3 : This query works fine. But problem is this , I need this query in Apache Superset as virtual table query. So If I declare the variable then it gives error on superset.

Error: Virtual dataset query cannot consist of multiple statements"

So please guide me how can I fix the problem?

CodePudding user response：

As @Larnu mentoined, you can CROSS JOIN the offset_table, since it only contains 1 value. I am assuming you need the GROUP BY to do an aggregation? If not, you do not need to use GROUP BY here at all. If you want to avoid getting duplicate records, use SELECT DISTINCT.

SELECT 
DATEPART(week, CAST(DATEADD(MINUTE, offset_table.offset, registration_time) as date)) as weeknumber
FROM 
registration
CROSS JOIN offset_table
GROUP BY
DATEPART(week, CAST(DATEADD(MINUTE, offset_table.offset, registration_time) as date));