SQL: single column that shows the average age

Question

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?

Mikhail Berlyant · Accepted Answer · 2021-08-16 16:45:03Z

1

Consider below approach

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

answered Aug 16, 2021 at 16:45

Mikhail Berlyant

174k10 gold badges173 silver badges251 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gordon Linoff · Accepted Answer · 2021-08-16 15:34:16Z

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;

Collectives™ on Stack Overflow

SQL: single column that shows the average age

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related