boolean value in column

[David McDivitt]

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

 

[David Gugick]

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

 

[--CELKO--]

SELECT
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;

 

[Roji. P. Thomas]

Look up CASE WHEN in BOL

 

[David McDivitt]

Date: Wed, 24 Aug 2005 13:17:27 -0400
Lines: 62

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

Thanks guys the CASE statement was what I was looking for.