SQL query aggregate function

[Ivan Smahin]

Hello william,

Friday, August 15, 2008, 7:53:54 PM, you wrote:

> When doing this aggregate function:
> select  d_cargo_pkgtype, d_cargo_description as theCargo,  (d_cargo_weight /
> d_cargo_pieceCount), d_cargo_defaultcargodesc_ref, count(*) as theCount
> from data_cargo join info_billoflading on cargo join info_clients on
> consignees
> where i_billoflading_clientShipper_ref = 8001
> group by d_cargo_pkgtype, d_cargo_description, (d_cargo_weight /
> d_cargo_pieceCount), d_cargo_defaultcargodesc_ref order by theCount desc,
> theCargo asc


> It doesn't work because the "group by" doesn't like the calculated (weight /
> pieces). I also tried making that an alias and the "group by" refused to see
> the alias even though the "order by" clause can see it.  I need the
> calculated result in the "group by" aggregate because the calculated group
> makes the rows have identical values.

> Can you make a suggestion for me to try?

There is a way - to make the method field.
ALter table t1 add column ( m1 double Method('d_cargo_weight /
d_cargo_pieceCount'));

select  d_cargo_pkgtype, d_cargo_description as theCargo,  m1, ....

...
Group by ... m1 ...
...


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com