1

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;
  • FYI that your query contains a cross join to cmve. You might already be aware but I just wanted to bring it to your attention in case you weren't. Some folks prefer ANSI style joins. Those make cross joins much more obvious. – Joe Obbish Feb 25 '17 at 0:27
  • Hi, thanks for suggestion. I have just started working on DB and will appreciate more suggestions. Is there any performance improvement in Joins than the conventional way? – Gibreel Abdullah Feb 26 '17 at 10:41
  • @JoeObbish Forgot to tag you. – Gibreel Abdullah Feb 26 '17 at 12:54
  • I don't know of a performance benefit. It's more of a style issue and a way to protect you against typos. Here's an article around SQL Server but the same principles apply for Oracle: sqlblog.com/blogs/aaron_bertrand/archive/2009/10/08/… – Joe Obbish Feb 27 '17 at 20:43
1

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)

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.