CODEWITHSUNDEEP
sqlsumsybaseaverage
karpar
karpar

Reputation: 3

SQL apply sum avg conditionally

Supposed we have the following table: 

index  value
1      55  
2      66  
3      77  
1      88  
3      99

how can i sum(value) when index=1 or index=3, and avg(value) when index=2 in one select statement?

Upvotes: 0

Views: 2436

Answers (2)

Wim
Wim

Reputation: 1096

SELECT sum(value) as agg,
       'S' as aggregateType
FROM DaTAble
WHERE index IN (1, 3)

UNION ALL

SELECT avg(value) as agg,
       'A' as aggregateType
FROM DaTAble
WHERE index = 2

Upvotes: 1

Arnaud F.
Arnaud F.

Reputation: 8452

Easily:

SELECT index, CASE WHEN index IN (1, 3) THEN SUM(value) ELSE AVG(value) END
FROM yourTable
GROUP BY index

Upvotes: 3

Related Questions