SQL single-group group function error/Not a group-by expression

Question

I'm trying to create a view, but when i include the "group by" function, it says that there is an error at line 2 saying that it is not a group-by expression. If i comment out the "group-by" function, then it says theres an single-group group function error in the view. how can i fix this?

CREATE or REPLACE VIEW bestseller_view
  AS SELECT e.empl_name,
            NVL((sum((si.amt_paid+a.ao_price)-fs.purchase_cost+a.ao_purchase_price)*se.salary_comm),0) sales_commission,
            COUNT(si.s_empl_id) vehicles_sold
 FROM for_sale fs
 JOIN sales_invoice si ON fs.vehicle_id = si.vehicle_id
 JOIN sales_empl se    ON si.s_empl_id = se.empl_id
 JOIN purchased_ao pao ON si.sales_invoice_id=pao.sales_invoice_id
 JOIN add_ons a        ON pao.ao_id=a.ao_id
 JOIN employee e       ON se.empl_id=e.empl_id;
 GROUP BY si.s_empl_id;

Alex Poole · Accepted Answer · 2013-11-17 09:43:34Z

2

The non-aggregate column in your select is e.empl_name, so you need to group by that, not si.s_empl_id;:

... SELECT e.empl_name,
  NVL((sum(...),0) sales_commission,
  COUNT(...) vehicles_sold
...
GROUP BY e.empl_name;

But since empl_name presumably isn't guaranteed to be unique, grouping by the ID makes more sense. You need to either select and group by both columns - though using e.empl_id might look more logical - and just ignore the ID you get back if it isn't relevant, in this case by just never selecting it from the view; or use this as an inner query and don't include it in the outer one. Seems like it makes more sense to have it in the view in case anyone does want/need it one day.

... SELECT e.empl_id, e.empl_name,
  NVL((sum(...),0) sales_commission,
  COUNT(...) vehicles_sold
...
GROUP BY e.empl_id, e.empl_name;

You also have your parentheses in the wrong place in your sum:

NVL((sum((si.amt_paid+a.ao_price)-fs.purchase_cost+a.ao_purchase_price)
     *se.salary_comm),0) sales_commission,

... is calculating the sum of all the (si.amt_paid+a.ao_price)-fs.purchase_cost+a.ao_purchase_price values, and then multiplying that sum by the se.salary_comm, which makes that column non-aggregate as well; I'm pretty sure that's not what you meant to do, so that should be:

NVL(sum((si.amt_paid+a.ao_price-fs.purchase_cost+a.ao_purchase_price)
  *se.salary_comm),0) sales_commission,

Or if there is a one-to-one relationship between e and se, which looks likely, you could leave that as it is and add se.salary_comm to the group by clause as @ypercube suggested.

edited Nov 17, 2013 at 9:43

answered Nov 17, 2013 at 8:53

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

ypercubeᵀᴹ Over a year ago

The se.salary_comm needs to be in the GROUP BY as well, it's not inside the SUM() aggregate.

Sully · Accepted Answer · 2013-11-17 06:58:14Z

1

 SELECT e.empl_name, si.s_empl_id
 ...
 GROUP BY e.empl_name, si.s_empl_id

answered Nov 17, 2013 at 6:58

Sully

15k5 gold badges56 silver badges83 bronze badges

2 Comments

Cate Over a year ago

I just tried that and got: ERROR at line 3: ORA-00979: not a GROUP BY expression

Sully Over a year ago

Sorry, that was not helpful. What happens if you remove the GROUP By and add 'SELECT DISTINCT si.s_empl_id'

Collectives™ on Stack Overflow

SQL single-group group function error/Not a group-by expression

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related