CODEWITHSUNDEEP
sqloracle-database
Heisenberg
Heisenberg

Reputation: 5299

How to aggregate functions with groupby in SQL

I have tables like this. I would like to groupbyand aggregate this in diffrent aggregate functions.

product sex  age
A        M    10
B        F    20
A        F    30
C        M    40

my desired result is like below. Now I can group in productkey, but in this case, I must group them byproductandsex.

Are there any way to achieve it?

   count(M) count(F) avarage(M) average(F)
A   1         1         10       30       
B   0         1         NA       20
C   1         0         40       NA

Thanks

Upvotes: 0

Views: 60

Answers (2)

Popeye
Popeye

Reputation: 35910

You can simply use the PIVOT as follows:

select * from your_Table
pivot
(count(1) as cnt, avg(age) as average for sex in ('M','F'))

Upvotes: 0

forpas
forpas

Reputation: 164099

With conditional aggregation:

select product,
  sum(case when sex = 'M' then 1 else 0 end),
  sum(case when sex = 'F' then 1 else 0 end),
  avg(case when sex = 'M' then age end),
  avg(case when sex = 'F' then age end)
from tablename
group by product

Upvotes: 1

Related Questions