Inspiring

Answered

Conditional values within a SQL function?

Forum|Forum|16 years ago
January 6, 2010
3 replies
1189 views

Hi there,

I don't know if this is even possible, but I have a client who has a fairly simple table of ratings for players in a sports league - a coach_rating and a league_rating. Typically, the logic is 'if the league rating is defined, use that; otherwise, use the coach rating' (this is so coach ratings may be overridden).

This was all well and good until the client decided that they want historical, statistical analysis done on 'the rating.' I can get the mean and standard deviation of one rating or the other; is there a way to tell SQL to 'take the average of the values in column A, but if there is a value in column B, substitute that value'?

I realize that I could simply pull the raw numbers out of the database and do the math in CF, but I'm trying to avoid that unless there's no other way.

Thanks!

Aq

Database access

This topic has been closed for replies.

Correct answer JR__Bob__Dobbs-qSBHQ2

You should be able to use a derived table to calculate your rating then do the stats on that result. In the sample below the derived table aliased as D is created based on a SELECT query that uses either LeagueRating or CoachRating as the value for Rating. LeagueRating is used if it is not null, else CoachRating is used. Then the standard deviation is calculated on the derived table D.

SELECT STDEV(Rating) AS Standard_Deviation_Of_Rating

FROM

(

SELECT COALESCE(LeagueRating, CoachRating) AS Rating

FROM Players

) AS D

Note that this sample is based on Microsoft SQL Server. You may wish to ask this question in forum specific to your database server type. For example, Microsoft SQL Server 2005 and newer have a Common Table Expression feature that could also be used in the scenario.

Quick example of derived tables.

http://www.sqlteam.com/article/using-derived-tables-to-calculate-aggregate-values

A

AquitaineAuthor

Inspiring

Nullif did the trick - didn't want to omit whole rows since we needed the other ratings. Thanks again!

A

AquitaineAuthor

Inspiring

Second part of this question, though much less urgent:

One of the ratings we're looking at is only given in some instances (for goalkeepers). Unfortunately, the way the input screen works, instead of NULL values when a player isn't a GK, they get a '0.'

Is there a way, within an expression like AVG or STDEVP, to tell it to exclude certain values? Like 'average this column but ignore values equal to zero'?

J

JR__Bob__Dobbs-qSBHQ2

Inspiring

Options:

1. Use the NULLIF t-sql function to replace 0 with NULL in your query.

2. Use a WHERE clause in your query to omit rows where some field = 0. Omitting rows from the STDEV calculation may not be desired. This depends on your requirements.

NULLIF

http://msdn.microsoft.com/en-us/library/ms177562.aspx

If this doesn't help you please post your table structure, query, sample data, and a sample desired results. Having more information will help us to help you.

D

Dan_Bracuk

Inspiring

It all sounds possible. The details depend on the db software. Function with names like ifnull or coalesce are what you want. Alternatively you can use a case contstruct.

select case

when somefield = somevalue then something

else somethingelse

end somealias

J

JR__Bob__Dobbs-qSBHQ2Correct answer

Inspiring

You should be able to use a derived table to calculate your rating then do the stats on that result. In the sample below the derived table aliased as D is created based on a SELECT query that uses either LeagueRating or CoachRating as the value for Rating. LeagueRating is used if it is not null, else CoachRating is used. Then the standard deviation is calculated on the derived table D.

SELECT STDEV(Rating) AS Standard_Deviation_Of_Rating

FROM

(

SELECT COALESCE(LeagueRating, CoachRating) AS Rating

FROM Players

) AS D

Note that this sample is based on Microsoft SQL Server. You may wish to ask this question in forum specific to your database server type. For example, Microsoft SQL Server 2005 and newer have a Common Table Expression feature that could also be used in the scenario.

Quick example of derived tables.

http://www.sqlteam.com/article/using-derived-tables-to-calculate-aggregate-values

A

AquitaineAuthor

Inspiring

This is on Microsoft SQL Server 2008.

Coalesce is exactly what I was looking for! Thank you!

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded