CODEWITHSUNDEEP
sqlgroup-byinformix
Santiago
Santiago

Reputation: 410

SQL - Group by an agregate function

I have a question whether if it's possible to make a group by an aggregate function.

Scenario:

I have a table which has biomass(kg) and number of individuals for everyday and a description, therefore I can calculate the total av. weight and total number of individuals within two dates as:

select 
  description, 
  sum(biomass)/sum(number_individuals) as av.weight, 
  sum(number_individuals) as individuals 
from 
  Table 
group by description

Which works okay, now, the thing is that I want to group those individuals separating them by weight ranges, in order to get something like:

description range(kg) number av.weigh(g)
Foo         2-3       2400    2584.48

I have tried something like 

SELECT 
  description, 
  case when sum(biomass)/sum(number_individuals) >= 2000.0 
            and sum(biomass)*1000/sum(number_individuals) < 3000 then '2-3'       
       else 'nothing' 
  end as desc_range
FROM Table 
Group by 
  description, 
  sum(biomass)/sum(number_individuals)

But it doesn't seem to work, neither using the alias desc_range ofc.

I am using Informix 9.40 TC3

Any help will be appreciated.

Best regards

Upvotes: 0

Views: 82

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271091

If you want to aggregate on an aggregation, you usually need a subquery. However, you mention individuals, so perhaps this is what you want:

select description,
        (case when biomass between 2 and 3 then '2-3'
              else 'nothing'
         end) as biomass
       sum(biomass)/sum(number_individuals) as av.weight, sum(number_individuals) as individuals
from Table
group by description,
         (case when biomass between 2 and 3 then '2-3'
               else 'nothing'
          end);

Upvotes: 1

Related Questions