topic aggregate function in Application Development and Automation Discussions

aggregate function

Former Member — Thu, 31 May 2007 11:23:34 GMT

Hi,

May I know aggregate function sum, count, max, min, average in select statement need to have group by?

I know sum need to have, what about others?

Thanks

Re: aggregate function

Former Member — Thu, 31 May 2007 11:25:18 GMT

If you are using aggregate functions, then its necessary for you to use <b>group by</b> clause.

Eg:

SELECT d.depid, d.name, COUNT(*) AS EMPLOYEE_COUNT

FROM department d JOIN employee e

d.DEPID = e.DEPID

GROUP BY d.depid, d.name

Regards

Raj

Re: aggregate function

Former Member — Thu, 31 May 2007 11:26:59 GMT

if you are using aggregate fn and other fields also in select statement then you have to use groupby for other fields.

suppose

select mblnr sum( menge ) into table itab from mseg where bwart = '101' group by mblnr.

since mblnr is not in aggregate fn so you have to use mblnr in group by clause (it may be any no of fields without aggregate fn) if you are using simply aggregate fn then no need of group by

select sum( menge ) into table itab from mseg where bwart = '101' .

regards

shiba dutta

Re: aggregate function

Former Member — Thu, 31 May 2007 11:27:13 GMT

if u r using more dan one field in da select clause den u hav to specify teh grp by caluse otherwise nt..

Re: aggregate function

Former Member — Thu, 31 May 2007 11:31:45 GMT

Hi,

I know if having other fields as well then other field need to have group by/order by for sum.

Can let me know if the rest like count, max, min, average also need to have group by /order by

thanks

points given.