Can i give an aggregate fn in query?

[2005 IR Help]

By: Abhilash K Narayanan - abhi_aict

Can i give an aggregate fn in query?

2004-06-13 13:22

Hi all,

 

Can i give an aggregate function in query like

 

"select deptname,sum(total_student) from department group by deptname"

 

if possible how can i specify the same in XML template. I tried with

 

<field name="deptname" class="java.lang.String"/>

<field name="sum(total_student)" class="java.lang.Integer"/>

 

but it is showing some NMTOKENS not valid error when compiling. Please help me...

 

regds,

abhi...

 

 

 

 

 

 

By: Shirley Goldrei - shirley

RE: Can i give an aggregate fn in query?

2004-06-13 14:48

Yes you can give an aggregate fn in a query.

 

Just call the field "sum_of_total_students" (or something like that). It is after all only a name that you refer to using $V{sum_of_total_students}. Your SQL statement looks fine.

 

The tokens that jasper doesn't like are the "(" and ")" you have in the name.

 

Hope ths helps,

Shirley

 

 

 

 

By: Shirley Goldrei - shirley

RE: Can i give an aggregate fn in query?

2004-06-13 14:52

Oops. Sorry the above should have read...

 

"a name that you refer to using $F{sum_of_total_students}"

F for field (not V for variable).

 

Shirley

 

 

 

 

By: Vinod Kumar Singh - vinodsingh

RE: Can i give an aggregate fn in query?

2004-06-13 21:15

Modify your query from-

select deptname,sum(total_student) from department group by deptname

 

To something like-

select deptname, sum(total_student) AS sum_of_total_students from department group by deptname

 

Now register this field function field as-

<field name="sum_of_total_students" class="java.lang.Integer"/>