Oracle – Getting ‘Not a Single-Group Group Function’ Error

oracle

Below is the query that I am running, but I am getting not a single-group group function error? Please explain why is this error coming and a possible solution to this.

SELECT 1                          AS "Contract",
  COUNT(DISTINCT(cmv.mdl_ver_id)) AS "Number Of Scenarios",
  COUNT(DISTINCT(cmvs.site_id))   AS "Number Of Sites",
  CASE
    WHEN cmve.driver_equip_id is null
    THEN COUNT(distinct(cmve.equip_mdl_id))
  END                             AS "Number of Driven",
  CASE
    WHEN cmve.driver_equip_id is not null
    THEN COUNT(distinct(cmve.equip_mdl_id))
  END                             AS "Number of Driver" 
 FROM cost_contract_mdl ccm,
  cost_model_ver cmv,
  cost_mdl_ver_site cmvs,
  cost_mdl_ver_equip cmve
WHERE ccm.mdl_id   =cmv.mdl_id
AND cmv.mdl_ver_id =cmvs.mdl_ver_id
AND cmv.mdl_ver_id =4281021;

Best Answer

If you don't use GROUP BY, then you can only use constant, and aggregate functions is the SELECT clause.

This is wrong:
CASE WHEN cmve.driver_equip_id is null THEN COUNT(distinct(cmve.equip_mdl_id)) END

CASE WHEN cmve.driver_equip_id is not null THEN COUNT(distinct(cmve.equip_mdl_id)) END

count (distinct case ... end) is probably what you need.

SELECT 1                          AS "Contract",
  COUNT(DISTINCT cmv.mdl_ver_id) AS "Number Of Scenarios",
  COUNT(DISTINCT cmvs.site_id)   AS "Number Of Sites",
  count (distinct CASE
    WHEN cmve.driver_equip_id is null
    THEN cmve.equip_mdl_id
  END)                             AS "Number of Driven",
  count (distinct CASE
    WHEN cmve.driver_equip_id is not null
    THEN cmve.equip_mdl_id
  END)                             AS "Number of Driver" 
 FROM cost_contract_mdl ccm,
  cost_model_ver cmv,
  cost_mdl_ver_site cmvs,
  cost_mdl_ver_equip cmve
WHERE ccm.mdl_id   =cmv.mdl_id
AND cmv.mdl_ver_id =cmvs.mdl_ver_id
AND cmv.mdl_ver_id =4281021;

P.s.

DISTINCT is not a function, it is a keyword, which makes the parentheses meaningless.

Instead of COUNT(DISTINCT(x))
It should be COUNT(DISTINCT x)

Related Solutions

ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’

UNDO is the area that stores the change vectors required to restore the data to the state that it was in at the beginning of the transaction.

The only cures for this are to reduce the amount of data that you're changing (by changing less data or reducing the transaction size), or to increase the available undo size.

Reducing the transaction size, which you'd do by committing more frequently, is problematic because you still have to deal with rolling back transactions that you've committed, and because it would also promote snapshot too old errors when the database cannot reconstruct from undo the data required for consistency with the start of a query.

So, as commenters say, the answer really is to have the DBA's adjust the size of UNDO available. Strangely, they can be reluctant to do so, or at least to do so adequately. If you currently have 256MB of UNDO and you ask for 2GB, they might see this as some huge amount that might in some way be undesirable and offer you 1GB. Smile indulgently at them, because you already doubled the amount that you really wanted and because there is no such thing as oversizing your UNDO tablespace.

Oracle – How to Generate Multiple Ranges Based on a Result Set

Solved this with help from https://stackoverflow.com/questions/3664903/how-can-i-return-multiple-identical-rows-based-on-a-quantity-field-in-the-row-it

The resulting answer with the original table looks like this:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
, results as (
  select n qty
  from qtys x
  join (
    select rownum n
    from dual
    connect by level <= (
      select max(qty)
      from qtys
    )
  ) y on y.n <= x.qty
)
select max(qty) qty
from (
  select count(1)
  , qty
  from results
  group by qty
  having count(1) = (
    select count(1)
    from qtys
  )
);

I'm certain I can condense it a little, but it produces the desired result.

EDIT

I suppose looking closer at this taking the minimum would have produced the same, desired result:

with qtys as (
  select 3 as qty from dual union all
  select 10 as qty from dual union all
  select 9 as qty from dual union all
  select 7 as qty from dual union all
  select 9 as qty from dual union all
  select 3 as qty from dual
)
select min(qty) qty
from qtys;

Best Answer

Related Solutions

ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’

Oracle – How to Generate Multiple Ranges Based on a Result Set

Related Question