boolean value in column

I need to do an aggregate query against an integer data type, and want to
return three columns based on the value: 1, 2, or 3. If the field name is
"Action", I should be able to sum (action=1), (action=2), and (action=3).
First I tried the following:
SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2), SUM(SU.[Action]
= 3)
The result of each would be -1 or zero. That doesn't work, I guess because
no boolean data type exists. Using CAST or CONVERT doesn't work either for
the same reason: (SU.[Action] = 1) cannot be interpreted. So thought I would
try IF/ELSE, but cannot get the syntax right. I've used it before but can't
remember how I did it. May have been with DB2. Should go something like:
SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF SU.[Action] = 3
BEGIN 1 END ELSE BEGIN 0 END)
If someone would enlighten me I'd appreciate it. ThanksSELECT
SUM CASE WHEN foobar_action = 1 then 1 ELSE 0 END) AS total_1,
SUM CASE WHEN foobar_action = 2 then 1 ELSE 0 END) AS total_2,
SUM CASE WHEN foobar_action = 3 then 1 ELSE 0 END) AS total_3
FROM Foobar;|||David McDivitt wrote:
> I need to do an aggregate query against an integer data type, and
> want to return three columns based on the value: 1, 2, or 3. If the
> field name is "Action", I should be able to sum (action=1),
> (action=2), and (action=3). First I tried the following:
> SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2),
> SUM(SU.[Action] = 3)
> The result of each would be -1 or zero. That doesn't work, I guess
> because no boolean data type exists. Using CAST or CONVERT doesn't
> work either for the same reason: (SU.[Action] = 1) cannot be
> interpreted. So thought I would try IF/ELSE, but cannot get the
> syntax right. I've used it before but can't remember how I did it.
> May have been with DB2. Should go something like:
> SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
> SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF
> SU.[Action] = 3 BEGIN 1 END ELSE BEGIN 0 END)
> If someone would enlighten me I'd appreciate it. Thanks
Try using a CASE statement:
create table #abc (action int)
insert into #abc values (1)
insert into #abc values (2)
insert into #abc values (2)
insert into #abc values (3)
insert into #abc values (3)
insert into #abc values (3)
Select
SUM( CASE action
WHEN 1 THEN 1
ELSE 0
END ) as "Action 1",
SUM( CASE action
WHEN 2 THEN 1
ELSE 0
END ) as "Action 2",
SUM( CASE action
WHEN 3 THEN 1
ELSE 0
END ) as "Action 3"
From #abc
Action 1 Action 2 Action 3
-- -- --
1 2 3
drop table #abc
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Look up CASE WHEN in BOL
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"David McDivitt" <x12code-del@.del-yahoo.com> wrote in message
news:ve9pg1dr6kvo45sc4s2mmmt003cisrhg6t@.
4ax.com...
>I need to do an aggregate query against an integer data type, and want to
> return three columns based on the value: 1, 2, or 3. If the field name is
> "Action", I should be able to sum (action=1), (action=2), and (action=3).
> First I tried the following:
> SELECT SU.SProc, SUM(SU.[Action] = 1), SUM(SU.[Action] = 2),
> SUM(SU.[Action]
> = 3)
> The result of each would be -1 or zero. That doesn't work, I guess because
> no boolean data type exists. Using CAST or CONVERT doesn't work either for
> the same reason: (SU.[Action] = 1) cannot be interpreted. So thought I
> would
> try IF/ELSE, but cannot get the syntax right. I've used it before but
> can't
> remember how I did it. May have been with DB2. Should go something like:
> SELECT SU.SProc, SUM(IF SU.[Action] = 1 BEGIN 1 END ELSE BEGIN 0 END),
> SUM(IF SU.[Action] = 2 BEGIN 1 END ELSE BEGIN 0 END), SUM(IF SU.[Action] =
> 3
> BEGIN 1 END ELSE BEGIN 0 END)
> If someone would enlighten me I'd appreciate it. Thanks
>|||>From: "David Gugick" <david.gugick-nospam@.quest.com>
>Date: Wed, 24 Aug 2005 13:17:27 -0400
>Lines: 62
>David McDivitt wrote:
>Try using a CASE statement:
>create table #abc (action int)
>insert into #abc values (1)
>insert into #abc values (2)
>insert into #abc values (2)
>insert into #abc values (3)
>insert into #abc values (3)
>insert into #abc values (3)
>Select
> SUM( CASE action
> WHEN 1 THEN 1
> ELSE 0
Thanks guys the CASE statement was what I was looking for.