MIN, MAX and "non-zero" functions?

I was somewhat flabbergasted to find that SQL Server doesn't appear to
include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
in a sub-select. Is there some way to simulate these?
What I'm actually trying to accomplish is to make a "it is zero if it's null":
TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
I thought by using MIN(theNumber, 0) I could get close enough. Is there some
easy way to accomplish this?
Maury
Maury Markowitz wrote:
> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury
Look up COALESCE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||COALESE will only return the first non-null value. Think of it as a shorthand
way of doing a CASE statement that checks each value in order for a NULL, and
returns the first value that isn't NULL.
This is not really the same as MIN, but if you want to substitue a value for
a NULL, try this:
TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
ISNULL(salesq4,0)
"Maury Markowitz" wrote:

> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there some
> easy way to accomplish this?
> Maury
|||"Tracy McKibben" wrote:

> Look up COALESCE in Books Online...
Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
my own or is there some oddly named function for this too?
Maury
|||Maury Markowitz wrote:
> "Tracy McKibben" wrote:
>
> Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
> my own or is there some oddly named function for this too?
> Maury
I've always used CASE myself...
SELECT
CASE WHEN x < y THEN x ELSE y END AS MinVal,
CASE WHEN x > y THEN x ELSE y END AS MaxVal
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||INTP56" wrote:
> TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
> ISNULL(salesq4,0)
Ahhhhh! I never read that entry because I always though it was shorthand for
"IS NULL"! Thanks!
Maury
|||Maury Markowitz wrote:
> INTP56" wrote:
> Ahhhhh! I never read that entry because I always though it was shorthand for
> "IS NULL"! Thanks!
> Maury
ISNULL has some well-known issues. COALESCE does essentially the same
thing as ISNULL, without the issues...
http://toponewithties.blogspot.com/2...lesce-and.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com
|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:63E71F36-D5DD-4B72-8708-EB9BE24EFDC8@.microsoft.com...
>I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
> its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's
> null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there
> some
> easy way to accomplish this?
> Maury
MIN(COALESCE(theNumber, 0))
Most aggregate functions eliminate NULL, since NULL represents an unknown
value (the exception is COUNT(*) which does not eliminate NULLs). If NULL
is being used as a placeholder for 0 in your data you might consider
replacing it with 0.

MIN, MAX and "non-zero" functions?

I was somewhat flabbergasted to find that SQL Server doesn't appear to
include a mathematical MAX or MIN. No, I can't use them as aggrs, because it
s
in a sub-select. Is there some way to simulate these?
What I'm actually trying to accomplish is to make a "it is zero if it's null
":
TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
I thought by using MIN(theNumber, 0) I could get close enough. Is there some
easy way to accomplish this?
MauryMaury Markowitz wrote:
> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's nu
ll":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there so
me
> easy way to accomplish this?
> Maury
Look up COALESCE in Books Online...
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||COALESE will only return the first non-null value. Think of it as a shorthan
d
way of doing a CASE statement that checks each value in order for a NULL, an
d
returns the first value that isn't NULL.
This is not really the same as MIN, but if you want to substitue a value for
a NULL, try this:
TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
ISNULL(salesq4,0)
"Maury Markowitz" wrote:

> I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's nu
ll":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there so
me
> easy way to accomplish this?
> Maury|||"Tracy McKibben" wrote:

> Look up COALESCE in Books Online...
Thanks! That solves that one. Now what about MIN and MAX? Do I have to roll
my own or is there some oddly named function for this too?
Maury|||Maury Markowitz wrote:
> "Tracy McKibben" wrote:
>
> Thanks! That solves that one. Now what about MIN and MAX? Do I have to rol
l
> my own or is there some oddly named function for this too?
> Maury
I've always used CASE myself...
SELECT
CASE WHEN x < y THEN x ELSE y END AS MinVal,
CASE WHEN x > y THEN x ELSE y END AS MaxVal
FROM MyTable
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||INTP56" wrote:
> TotalSales = ISNULL(salesq1,0) + ISNULL(salesq2,0) + ISNULL(salesq3,0) +
> ISNULL(salesq4,0)
Ahhhhh! I never read that entry because I always though it was shorthand for
"IS NULL"! Thanks!
Maury|||Maury Markowitz wrote:
> INTP56" wrote:
> Ahhhhh! I never read that entry because I always though it was shorthand f
or
> "IS NULL"! Thanks!
> Maury
ISNULL has some well-known issues. COALESCE does essentially the same
thing as ISNULL, without the issues...
http://toponewithties.blogspot.com/...realsqlguy.com|||"Maury Markowitz" <MauryMarkowitz@.discussions.microsoft.com> wrote in
message news:63E71F36-D5DD-4B72-8708-EB9BE24EFDC8@.microsoft.com...
>I was somewhat flabbergasted to find that SQL Server doesn't appear to
> include a mathematical MAX or MIN. No, I can't use them as aggrs, because
> its
> in a sub-select. Is there some way to simulate these?
> What I'm actually trying to accomplish is to make a "it is zero if it's
> null":
> TotalSales = nz(salesq1) + nz(salesq3) + nz(salesq3) + nz(salesq4)
> I thought by using MIN(theNumber, 0) I could get close enough. Is there
> some
> easy way to accomplish this?
> Maury
MIN(COALESCE(theNumber, 0))
Most aggregate functions eliminate NULL, since NULL represents an unknown
value (the exception is COUNT(*) which does not eliminate NULLs). If NULL
is being used as a placeholder for 0 in your data you might consider
replacing it with 0.