Prev: Opposite to INNER JOIN
Next: Export table with Cursor
From: Simon Woods on 5 Mar 2010 04:40
Hi

Suppose I have the following data in a column (if is sample data, but
the DB I am working with has some specific values to indicate other data
properties - I fully accept that there may well be better ways to
indicate this).

Elapsed Days
1
2
3
4
5
6
7
8
NULL
-99999
-88888

What SQL can I write so that I can eliminate the -99999 and -88888 for
the purposes of min/max/avg/sum yet include them for the purposes of count

So my stats would be

count=10
min=1
max=8
sum=36
avg=3.6

Ideally I need to be able to do this in a single SQL statement.

I have had a go at this but not been able to see a straight forward way
through. e.g. I'd wondered about replacing the -99999/-88888
a) with nulls, but that throws the count out.
b) with min or max, but that throws the avg/sum out
c) with avg, but that throws the sum out.

Perhaps there's a more obvious way which I'm missing!

Many thx

Simon
From: Plamen Ratchev on 5 Mar 2010 10:30
You can use CASE expressions to handle each aggregate function:

SELECT SUM(CASE WHEN elapsed_days IN (-99999, -88888) THEN 0 ELSE elapsed_days END) AS sum_value,
MAX(CASE WHEN elapsed_days IN (-99999, -88888) THEN NULL ELSE elapsed_days END) AS max_value,
MIN(CASE WHEN elapsed_days IN (-99999, -88888) THEN NULL ELSE elapsed_days END) AS min_value,
COUNT(elapsed_days) AS count_value,
AVG(CASE WHEN elapsed_days IN (-99999, -88888) THEN NULL ELSE elapsed_days END) AS avg_value
FROM Foo;

--
Plamen Ratchev
http://www.SQLStudio.com
From: Simon Woods on 5 Mar 2010 11:06
On 05/03/2010 15:30, Plamen Ratchev wrote:
> You can use CASE expressions to handle each aggregate function:
>
> SELECT SUM(CASE WHEN elapsed_days IN (-99999, -88888) THEN 0 ELSE
> elapsed_days END) AS sum_value,
> MAX(CASE WHEN elapsed_days IN (-99999, -88888) THEN NULL ELSE
> elapsed_days END) AS max_value,
> MIN(CASE WHEN elapsed_days IN (-99999, -88888) THEN NULL ELSE
> elapsed_days END) AS min_value,
> COUNT(elapsed_days) AS count_value,
> AVG(CASE WHEN elapsed_days IN (-99999, -88888) THEN NULL ELSE
> elapsed_days END) AS avg_value
> FROM Foo;
>

Excellent ... and I'd been playing around with that as well!

Thx vm Plamen

S
 | 
Pages: 1
Prev: Opposite to INNER JOIN
Next: Export table with Cursor