CODEWITHSUNDEEP
oracle-databasefunctiongroup-by
J2H656
J2H656

Reputation: 101

Not a Group By Expression, with own declared functions

Ok so I know when using aggregate functions such as MAX, MIN, AVG and so fort, in a select statement. You need to use the GROUP BY function for all the selected columns that DON'T use the aggregate functions.EX

SELECT name, MAX(age)
FROM person
GROUP BY name

but my issue is, when I use my own functions for certain columns and I use an Aggregate function within my select statement. EX

SELECT f_fullname(name, surname) as fullname, max(age)
FROM person

Should i add the whole function as a part of the group by clause?

GROUP BY f_fullname(name, surname)

because at this moment i get the ORA-00979 not a GROUP BY expression error.

Thanks for your help!

PS. the select statements are just for explanation purposes**

Upvotes: 0

Views: 99

Answers (1)

A.B.Cade
A.B.Cade

Reputation: 16905

You can either have the whole function or the columns which are the parameters.

select f_fullname(name , surname) full_name, max (age)
  from person
group by name, surname;

or

select f_fullname(name , surname) full_name, max (age)
  from person
group by f_fullname(name , surname);

Here is a sqlfiddle demo

Upvotes: 1

Related Questions