Invalid request 'group by' (oracle)-CodePudding

Please tell me how to fix the error. An error Expression not in GROUP BY key 'isin'.

I understand that I am doing the grouping incorrectly, but I do not know how to redo the code for this request correctly. Here you need to find the maximum value of end_circ and the minimum value of begin_circ for the key stocks_full_id. It is necessary to display all columns from select together with max and min.

SELECT a.isin as id,
       a.state_number as number,
       a.update_time as valid_from_date,
       '2999-12-31 00:00:00' as valid_to_date,
       a.operdate as oper,
       a.inn as inn_num,
       a.name_eng as name,
       coalesce(ts.full_name_eng,a.name_eng) as full_nm,
       max (stg.end_circ) as end_date,
       min (stg.begin_circ) as start_date,
       case when sk.name_eng IS NULL then sk.name_uk else sk.name_eng end as subtype_nm
FROM (SELECT s.*, rank() over (PARTITION BY isin,state_number ORDER BY operdate desc) as rn
      FROM stocks s 
      WHERE isin IS NOT NULL and state_number IS NOT NULL) a
JOIN trading_stocks ts ON ts.emission_is=a.id
JOIN stocks_trading_grounds stg ON stg.stocks_full_id=a.id
JOIN stocks_kinds sk ON sk.id=a.kind_id
WHERE stg.end_circ >= "2021-01-01 00:00:00" and a.rn=1
GROUP BY stg.stocks_full_id

CodePudding user response：

GROUP BY for the individual table within the JOIN:

SELECT a.isin as id,
       a.state_number as number,
       a.update_time as valid_from_date,
       '2999-12-31 00:00:00' as valid_to_date,
       a.operdate as oper,
       a.inn as inn_num,
       a.name_eng as name,
       coalesce(ts.full_name_eng,a.name_eng) as full_nm,
       stg.end_date,
       stg.start_date,
       case when sk.name_eng IS NULL then sk.name_uk else sk.name_eng end as subtype_nm
FROM   (SELECT s.*,
               rank() over (PARTITION BY isin,state_number ORDER BY operdate desc) as rn
        FROM stocks s 
        WHERE isin IS NOT NULL
        and state_number IS NOT NULL
       ) a
       JOIN trading_stocks ts ON ts.emission_is=a.id
       JOIN stocks_kinds sk ON sk.id=a.kind_id
       JOIN (
         SELECT stocks_full_id,
                max(stg.end_circ) as end_date,
                min(stg.begin_circ) as start_date
         FROM   stocks_trading_grounds
         GROUP BY stocks_full_id
       ) stg
       ON stg.stocks_full_id=a.id
WHERE  stg.end_date >= DATE '2021-01-01'
AND    a.rn=1

Also, double quotes are not used for a string literal; they are used for a quoted identifier. Either use single quotes '2021-01-01 00:00:00' for a string literal or DATE '2021-01-01' for a date literal.

CodePudding user response：

You have to include all the non-aggregated columns in your group by clause. So you updated query would be -

SELECT a.isin as id,
       a.state_number as number,
       a.update_time as valid_from_date,
       '2999-12-31 00:00:00' as valid_to_date,
       a.operdate as oper,
       a.inn as inn_num,
       a.name_eng as name,
       coalesce(ts.full_name_eng,a.name_eng) as full_nm,
       max (stg.end_circ) as end_date,
       min (stg.begin_circ) as start_date,
       case when sk.name_eng IS NULL then sk.name_uk else sk.name_eng end as subtype_nm
FROM (SELECT s.*, rank() over (PARTITION BY isin,state_number ORDER BY operdate desc) as rn
      FROM stocks s 
      WHERE isin IS NOT NULL and state_number IS NOT NULL) a
JOIN trading_stocks ts ON ts.emission_is=a.id
JOIN stocks_trading_grounds stg ON stg.stocks_full_id=a.id
JOIN stocks_kinds sk ON sk.id=a.kind_id
WHERE stg.end_circ >= '2021-01-01 00:00:00' and a.rn=1
GROUP BY a.isin,
         a.state_number,
         a.update_time,
         a.operdate,
         a.inn,
         a.name_eng,
         coalesce(ts.full_name_eng,a.name_eng),
         case when sk.name_eng IS NULL then sk.name_uk else sk.name_eng end;