CODEWITHSUNDEEP
sqlsql-servert-sql
ILoveYouTooPizza
ILoveYouTooPizza

Reputation: 3

Average of CASE WHEN field

How can I get the average of the column TotalTeamMembership?

SELECT Player,
CASE WHEN Basketball = '' THEN 0 ELSE 1 END + 
CASE WHEN Baseball = '' THEN 0 ELSE 1 END + 
CASE WHEN Football = '' THEN 0 ELSE 1 END AS TotalTeamMembership
FROM PlayerMembership;

This is what I currently get:

Progress

This is what I need:

Goal

Thank you for any guidance!

Upvotes: 0

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270443

You can use AVG():

SELECT AVG(CASE WHEN Basketball = '' THEN 0.0 ELSE 1 END + 
           CASE WHEN Baseball = '' THEN 0 ELSE 1 END + 
           CASE WHEN Football = '' THEN 0 ELSE 1 END
           ) AS avg_TotalTeamMembership
FROM PlayerMembership;

Upvotes: 3

Lukasz Szozda
Lukasz Szozda

Reputation: 175954

You could use AVGaggregate function:

SELECT AVG(TotalTeamMembership)
FROM (SELECT Player,
       CASE WHEN Basketball = '' THEN 0 ELSE 1 END + 
       CASE WHEN Baseball = '' THEN 0 ELSE 1 END + 
       CASE WHEN Football = '' THEN 0 ELSE 1 END AS TotalTeamMembership
      FROM PlayerMembership) s;

Upvotes: 1

Related Questions