List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 28 1999 3:38pm
Subject:Re: Pls. help me translate this query.
View as plain text  
On Mon, 1999-09-27 10:20:00 -0400, Bob Kline wrote:
> > Please help me translate this query to SQL query.
> > Thank you in advance.
> > 
> > SELECT Table1.Field1, Count(IIf([Field3]=55,[field2],Null)) AS a,
> > Sum(IIf([Field3]=45,[field2],Null)) AS b
> > FROM Table1
> > GROUP BY Table1.Field1;
> > 
> > Bill
> 
> Hi, Bill:
> 
> SELECT  Table1.Field1,
>         COUNT
>         (
>             CASE Field3
>                 WHEN 55 THEN field2
>                 ELSE         NULL
>             END
>         ) AS a,
>         SUM
>         (
>             CASE Field3
>                 WHEN 45 THEN field2
>                 ELSE         NULL
>             END
>         ) AS b
> FROM    Table1
> GROUP BY Field1;

And if you're using a MySQL version before V3.23.x, which doesn't have
the CASE statement, just use IF (that's exactly the equivalent to
Access' "immediate if"):

  SELECT Field1,
         COUNT( IF(Field3=55, field2, NULL) ) AS a,
         COUNT( IF(Field3=45, field2, NULL) ) AS b
  FROM Table1
  GROUP BY Field1;

Note, that you get the same results with
                IF(Field3=55, 1, NULL)
because COUNT only counts the number of rows, the actual value doesn't
matter.

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
Pls. help me translate this query.mysql xu27 Sep
  • RE: Pls. help me translate this query.bkline27 Sep
    • Re: Pls. help me translate this query.Martin Ramsch28 Sep