CODEWITHSUNDEEP
sqlsqlite
Gaurav
Gaurav

Reputation: 2103

how to calculate multiple aggregates on multiple columns

I want to create summary of my table with aggragate functions such as max,min,avg The query is something like this 

select 'avg',avg(column1) as 'avg_resp',avg(col2) as 'ncount' from table
union all
select 'max',max(column1),max(col2) from table
union all 
select 'min',min(column1),min(col2) from table;

Can it be done in better way??
PS: I want to use it for REST API so I am trying not to transpose/elongate the final result.

REST API model from my current code:

{ 
 avg_resp:[min,max,avg],
 ncount: [min,max,avg]
}

Upvotes: 0

Views: 58

Answers (3)

Nitu Bansal
Nitu Bansal

Reputation: 3856

yes it is possible 

  select avg(column1) as avg_resp,
   avg(col2) as ncount,
   max(column1) as max_col1,
   max(col2) as max_col2,
   min(column1) as min_col1,
   min(col2) as min_col2
  from table

Upvotes: 0

juergen d
juergen d

Reputation: 204854

You can put everything in one select statement

select avg(column1) as avg_resp,
       avg(col2) as ncount,
       max(column1) as max_col1,
       max(col2) as max_col2,
       min(column1) as min_col1,
       min(col2) as min_col2
from your_table

Upvotes: 1

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Why not:

select 
  avg(column1) as 'avg_resp', 
  avg(col2)    as 'ncount',
  max(column1) AS Max1,
  max(col2)    AS MAx2, 
  min(column1) AS Min1,
  min(col2)    AS Min2
from table

Upvotes: 1

Related Questions