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.

Bitwise or aggregate function

Hi,
I understand there are no aggregate function for ORing multiple row columns
together. We have a access control system that looks like
CREATE TABLE AccessTable
(
UserID int NOT NULL PRIMARY KEY,
EntityID int NOT NULL PRIMARY KEY,
AccessMask int NOT NULL
)
where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
etc. We would like to be able to do something like
select AGGR_ORSUM(AccessMask) As AggSum
from AccessTable
where EntityID = @.EntityID
I've seen some samples of a two table solution but I just wanted to double
check if someone has a better solution. Running SQL Server 2005.
Thanks,
MansoManso wrote:
> Hi,
> I understand there are no aggregate function for ORing multiple row column
s
> together. We have a access control system that looks like
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL PRIMARY KEY,
> EntityID int NOT NULL PRIMARY KEY,
> AccessMask int NOT NULL
> )
> where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
> etc. We would like to be able to do something like
> select AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = @.EntityID
> I've seen some samples of a two table solution but I just wanted to double
> check if someone has a better solution. Running SQL Server 2005.
> Thanks,
> Manso
Why would you store this data as a bitmap in the first place? Try:
SELECT
MAX(AccessMask & 1)+
MAX(AccessMask & 2)+
MAX(AccessMask & 4)+
MAX(AccessMask & 8)+
MAX(AccessMask & 16)+
MAX(AccessMask & 32)+
MAX(AccessMask & 64)+
MAX(AccessMask & 128) AS aggr_orsum
FROM AccessTable
WHERE EntityID = @.EntityID ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Manso" <Manso@.discussions.microsoft.com> wrote in message
news:C61C4BDC-BFAD-4B20-B379-3504674E28A4@.microsoft.com...
> Hi,
> I understand there are no aggregate function for ORing multiple row
> columns
> together. We have a access control system that looks like
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL PRIMARY KEY,
> EntityID int NOT NULL PRIMARY KEY,
> AccessMask int NOT NULL
> )
> where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
> etc. We would like to be able to do something like
> select AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = @.EntityID
> I've seen some samples of a two table solution but I just wanted to double
> check if someone has a better solution. Running SQL Server 2005.
>
Well, it so happens SQL Server 2005 supports User-Defined Aggregate
functions.
For instance
--Aggregate.cs--
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate(Format.Native,
IsInvariantToDuplicates=true,
IsInvariantToNulls = true,
IsInvariantToOrder = true,
IsNullIfEmpty = false)]
public struct AGGR_ORSUM
{
int accumulator;
public void Init()
{
accumulator = 0;
}
public void Accumulate(SqlInt32 Value)
{
if (Value.IsNull)
return;
accumulator |= Value.Value;
}
public void Merge(AGGR_ORSUM Group)
{
accumulator |= Group.accumulator;
}
public SqlInt32 Terminate()
{
return new SqlInt32(accumulator);
}
}
--end Aggregate.cs--
--Test.sql--
\
drop table AccessTable
CREATE TABLE AccessTable
(
UserID int NOT NULL,
EntityID int NOT NULL ,
AccessMask int NOT NULL
)
insert into AccessTable(UserID, EntityID,AccessMask) values (1,1,2)
insert into AccessTable(UserID, EntityID,AccessMask) values (2,1,16)
insert into AccessTable(UserID, EntityID,AccessMask) values (3,1,2)
select dbo.AGGR_ORSUM(AccessMask) As AggSum
from AccessTable
where EntityID = 1
--end Test.sql--
David|||Thanks for your prompt reply David.
Because it's a convenient way of representing access masks. I know it has a
few drawbacks but the upside is we can append new access bits without adding
columns or modifying the schema. Since we're never using the access column i
n
a where clause I find it to be the best option. We have about 13 different
access types today but that will increase.
I just managed to put together a function that does what we want:
declare @.Return int
set @.Return = 0
select @.Return = @.Return | AccessMask
from AccessTable
where where EntityID = @.EntityID
return @.Return
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> Why would you store this data as a bitmap in the first place? Try:
> SELECT
> MAX(AccessMask & 1)+
> MAX(AccessMask & 2)+
> MAX(AccessMask & 4)+
> MAX(AccessMask & 8)+
> MAX(AccessMask & 16)+
> MAX(AccessMask & 32)+
> MAX(AccessMask & 64)+
> MAX(AccessMask & 128) AS aggr_orsum
> FROM AccessTable
> WHERE EntityID = @.EntityID ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Manso wrote:
> Thanks for your prompt reply David.
> Because it's a convenient way of representing access masks. I know it has
a
> few drawbacks but the upside is we can append new access bits without addi
ng
> columns or modifying the schema. Since we're never using the access column
in
> a where clause I find it to be the best option. We have about 13 different
> access types today but that will increase.
How about :
CREATE TABLE AccessTable
(
UserID int NOT NULL,
EntityID int NOT NULL,
AccessTypeID int NOT NULL CHECK AccessType BETWEEN (1 AND 13),
PRIMARY KEY (UserID, EntityID, AccessTypeID)
)

> I just managed to put together a function that does what we want:
> declare @.Return int
> set @.Return = 0
> select @.Return = @.Return | AccessMask
> from AccessTable
> where where EntityID = @.EntityID
> return @.Return
>
That gives an undefined result because it's a multi-row assignment,
which isn't officially supported. For that reason it may break under
some conditions. Also, it isn't a general aggregation solution because
it cannot GROUP.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Beautiful, David. Thanks a lot. We've been really indecisive whether to use
CLR in this project but maybe we should look into it...looks .
Thanks,
Manso
"David Browne" wrote:

> "Manso" <Manso@.discussions.microsoft.com> wrote in message
> news:C61C4BDC-BFAD-4B20-B379-3504674E28A4@.microsoft.com...
> Well, it so happens SQL Server 2005 supports User-Defined Aggregate
> functions.
> For instance
> --Aggregate.cs--
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
>
> [Serializable]
> [SqlUserDefinedAggregate(Format.Native,
> IsInvariantToDuplicates=true,
> IsInvariantToNulls = true,
> IsInvariantToOrder = true,
> IsNullIfEmpty = false)]
> public struct AGGR_ORSUM
> {
> int accumulator;
> public void Init()
> {
> accumulator = 0;
> }
> public void Accumulate(SqlInt32 Value)
> {
> if (Value.IsNull)
> return;
> accumulator |= Value.Value;
> }
> public void Merge(AGGR_ORSUM Group)
> {
> accumulator |= Group.accumulator;
> }
> public SqlInt32 Terminate()
> {
> return new SqlInt32(accumulator);
> }
>
> }
> --end Aggregate.cs--
> --Test.sql--
> \
> drop table AccessTable
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL,
> EntityID int NOT NULL ,
> AccessMask int NOT NULL
> )
> insert into AccessTable(UserID, EntityID,AccessMask) values (1,1,2)
> insert into AccessTable(UserID, EntityID,AccessMask) values (2,1,16)
> insert into AccessTable(UserID, EntityID,AccessMask) values (3,1,2)
> select dbo.AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = 1
> --end Test.sql--
> David
>
>|||I left out the check constraint for clarity.
I read somewhere else that this could give unwanted results somewhere else
but I haven't managed to find out under which circumstances. Do you know
when/why/how this could break?
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> How about :
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL,
> EntityID int NOT NULL,
> AccessTypeID int NOT NULL CHECK AccessType BETWEEN (1 AND 13),
> PRIMARY KEY (UserID, EntityID, AccessTypeID)
> )
>
> That gives an undefined result because it's a multi-row assignment,
> which isn't officially supported. For that reason it may break under
> some conditions. Also, it isn't a general aggregation solution because
> it cannot GROUP.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Manso wrote:
> I left out the check constraint for clarity.
But in my example you don't need the bitmap (because I changed the
key).

> I read somewhere else that this could give unwanted results somewhere else
> but I haven't managed to find out under which circumstances. Do you know
> when/why/how this could break?
Books Online (2000 and 2005) says
"If the SELECT statement returns more than one value, the variable is
assigned the last value returned."
So if your "aggregate" solution works it is arguably a bug!
The following KB confirms that "The correct behavior for an aggregate
concatenation query is undefined." So you are definitely on thin ice if
you rely on it.
http://support.microsoft.com/kb/287515/en-us
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"The correct behavior for an aggregate concatenation query is undefined."
This classic contrived phrase was written by some still unknown fuzzball
confirmed by
one intelligent MS employee.It is meaningless and that's putting it kindly:)
Perhaps it was Celko:) My guess is that since it came from Sybase MS thinks
the less said
about it the better.Aside from the functionality,which boils down to 'it
depends',
it is another example that 'words due matter':)
For interested readers see what sybase says about it (especially in Update)
in their
enterprise db.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145543785.636307.71580@.i39g2000cwa.googlegroups.com...
> Manso wrote:
> But in my example you don't need the bitmap (because I changed the
> key).
>
> Books Online (2000 and 2005) says
> "If the SELECT statement returns more than one value, the variable is
> assigned the last value returned."
> So if your "aggregate" solution works it is arguably a bug!
> The following KB confirms that "The correct behavior for an aggregate
> concatenation query is undefined." So you are definitely on thin ice if
> you rely on it.
> http://support.microsoft.com/kb/287515/en-us
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Writing assembly language in SQL will come back and get you. Use a
normalized table design with one column for each access privilege or a
hierarchy of privileges in a nested sets model.
Also, you had two PRIMARY KEYs!