Issue with group by statement-CodePudding

I am trying to run this query but I keep getting this error: ORA-00937: not a single-group group function 00937. 00000 - "not a single-group group function". How should I Group By in this case?

select trunc(dstamp) "DATE", COUNT(CODE), user_id,  dstamp - lag(dstamp) over (partition by user_id order by dstamp) as elapsed, 
ROUND(sum(update_qty / substr(sku_id, instr(sku_id, '-', 1, 1)  1 , instr(sku_id, '-', 1, 2) - 1 - instr(sku_id, '-', 1, 1)))) "CASES_PICKED",
from v_inventory_transaction
where client_id = 'USKIDS2CA' 
and code = 'Pick' 
and list_id IS NOT NULL 
and STATION_ID LIKE 'R%' 
and reference_id not like '%-FK%' 
AND trunc(dstamp) = '03/23/2022'
GROUP BY ***????***

CodePudding user response：

Your problem is the usage of the analytic function lag in the GROUP BYstatement.

The correct approach is to first calculate the column with the lag

SELECT trunc(dstamp) "DATE", user_id, 
dstamp - lag(dstamp) over (partition by user_id order by dstamp) as elapsed
FROM tab

and in the second step use it in some aggregate function - here example with max

WITH dt AS (
SELECT trunc(dstamp) "DATE", user_id, 
dstamp - lag(dstamp) over (partition by user_id order by dstamp) as elapsed
FROM tab
)
SELECT  "DATE", user_id, max(elapsed)
FROM dt
GROUP BY "DATE", user_id

CodePudding user response：

If you remove all aggregations/calculations temporarily

, trunc(dstamp) - lag(trunc(dstamp)) OVER (
    PARTITION BY user_id ORDER BY trunc(dstamp)
    ) AS elapsed
, COUNT(CODE)
, ROUND(sum(update_qty / ??? )) "CASES_PICKED"

it will help you focus on the underlying needs of the group by clause, which without those calculations is almost a replica of the select clause (but also without any column aliases).

SELECT
      trunc(dstamp) "DATE"
    , user_id
    , substr(sku_id, instr(sku_id, '-', 1, 1)   1
            , instr(sku_id, '-', 1, 2) - 1 - instr(sku_id, '-', 1, 1)) "SUBSTRING"
FROM v_inventory_transaction
WHERE client_id = 'USKIDS2CA'
    AND code = 'Pick'
    AND list_id IS NOT NULL
    AND STATION_ID LIKE 'R%'
    AND reference_id NOT LIKE '%-FK%'
    AND trunc(dstamp) = '03/23/2022'
GROUP BY
      trunc(dstamp)
    , user_id
    , substr(sku_id, instr(sku_id, '-', 1, 1)   1
            , instr(sku_id, '-', 1, 2) - 1 - instr(sku_id, '-', 1, 1))

However there are at least these problems to overcome if we attempt to finish the query

it isn't clear (without sample data and expected result) to know if you need the "elapsed" calculation performed before the grouping or if it is sufficient to do this using the truncated date.
you cannot divide a number by a string eg 100/substr('bad',1,2) is bad and produces ORA-01722: invalid number as a result. You could try casting the substring to a numeric - but this could fail also if the string can be converted.
compare the truncated date to a date, not a string e.g. trunc(dstamp) = date '2022-03-23' or use to_date()

Suggested:

SELECT
      trunc(dstamp) "DATE"
    , user_id
    , trunc(dstamp) - lag(trunc(dstamp)) OVER (
        PARTITION BY user_id ORDER BY trunc(dstamp)
        ) AS elapsed
    , COUNT(CODE)
    , ROUND(sum(update_qty / cast(substr(sku_id, instr(sku_id, '-', 1, 1)   1
        , instr(sku_id, '-', 1, 2) - 1 - instr(sku_id, '-', 1, 1)) as integer))) "CASES_PICKED"
FROM v_inventory_transaction
WHERE client_id = 'USKIDS2CA'
    AND code = 'Pick'
    AND list_id IS NOT NULL
    AND STATION_ID LIKE 'R%'
    AND reference_id NOT LIKE '%-FK%'
    AND trunc(dstamp) = to_date('03/23/2022','mm/dd/yyyy')
GROUP BY
      trunc(dstamp)
    , user_id
    , cast(substr(sku_id, instr(sku_id, '-', 1, 1)   1
         , instr(sku_id, '-', 1, 2) - 1 - instr(sku_id, '-', 1, 1)) as integer)

note: to use the lag function you should refer to trunc(dstamp) throughout otherwise you would need to group by the un-truncated dstamp

see: db<>fiddle here