Thread: If function available?

I am looking to do something like:

SELECT
Field1
, COUNT( IF( Field1 = 1, Field1, NULL ) ) AS one
, SUM( IF( Field1 = 2, Field1, NULL ) ) AS two
FROM
Table
GROUP BY
Field1

I tried it from the psql prompt and didn't work. Is there an IF or
IIF function that can be used on a select query?

Francisco,

> I tried it from the psql prompt and didn't work. Is there an IF or
> IIF function that can be used on a select query?

You want CASE, as in:

CASE WHEN field1 = 1 THEN NULL ELSE field1 END

See Postgresql.org --> Documentation --> SQL Commands --> SELECT for
more information, or consult your favorite SQL92 manual.

There are also several special functions for dealing with NULL output,
such as:

COALESCE(x,y,z) : returns first non-null value
NULLIF(x, y) : returns NULL if x = y

-Josh

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

On Tue, 20 Nov 2001, Josh Berkus wrote:

> You want CASE, as in:
> CASE WHEN field1 = 1 THEN NULL ELSE field1 END
>
> See Postgresql.org --> Documentation --> SQL Commands --> SELECT for
> more information, or consult your favorite SQL92 manual.

No luck. Neither the online "\h select" or the online docs at the web site
even mention CASE.

Tried searching for an online SQL 92 manual and didn't find any.

Any pointers on how to use the CASE statement or a URL for a general SQL
92 reference?

On Tue, 20 Nov 2001, Francisco Reyes wrote:

> > You want CASE, as in:
> > CASE WHEN field1 = 1 THEN NULL ELSE field1 END
> >
> > See Postgresql.org --> Documentation --> SQL Commands --> SELECT for
> > more information, or consult your favorite SQL92 manual.
>
> No luck. Neither the online "\h select" or the online docs at the web site
> even mention CASE.
>
> Tried searching for an online SQL 92 manual and didn't find any.
>
> Any pointers on how to use the CASE statement or a URL for a general SQL
> 92 reference?

http://www.postgresql.org/idocs/index.php?functions-conditional.html

-- Brett
                                          http://www.chapelperilous.net/
------------------------------------------------------------------------
You should emulate your heros, but don't carry it too far.  Especially
if they are dead.