CODEWITHSUNDEEP
sqlgoogle-bigquery
user16228179
user16228179

Reputation: 59

SQL: single column that shows the average age

I have a table that looks like the following:

categories age
cat_1 12
cat_2 32
cat_1 14
cat_2 25
cat_1 17

What I am trying to do is to have the category names as columns and then the value should be the average of that category. Something similar to the following:

cat_1 cat_2
14.33 28.5

I have tried the following code but it doesn't give me them as columns.

select 
    avg(case when category = 'cat_1' then age end) as cat_1
from table
group by category

Could anyone help?

Upvotes: 0

Views: 128

Answers (2)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172994

Consider below approach

select *
from `project.dataset.table`
pivot (avg(age) for categories in ('cat_1', 'cat_2'))

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You don't want group by:

select avg(case when category = 'cat_1' then age end) as cat_1,
       avg(case when category = 'cat_2' then age end) as cat_2       
from table;

Upvotes: 0

Related Questions