Aggregating Boolean Expressions - SUM(([Field] = 'value') + ([Field] = 'value'))

Aggregating Boolean Expressions - SUM(([Field] = 'value') + ([Field] = 'value'))

Post by John Smit » Wed, 28 Feb 2001 01:03:11



Hello

This is a technique I use alot in Jet SQL because it is very quick and
powerful.

However SQLServer thinks that [Field] = 'value' is an assignment rather than
an expression so it freaks out.

Any ideas what the correct syntax is?

Cheers

 
 
 

Aggregating Boolean Expressions - SUM(([Field] = 'value') + ([Field] = 'value'))

Post by Itzik Ben-Ga » Wed, 28 Feb 2001 01:25:10


You can translate the expression col1 = <val1> to

CASE WHEN col1 = <val1> THEN 1 ELSE 0 END

Or in an aggregate function:

SUM(CASE WHEN col1 = <val1> THEN 1 ELSE 0 END)

--
BG

Hi-Tech College, Israel
http://sql.hi-tech.co.il


Quote:> Hello

> This is a technique I use alot in Jet SQL because it is very quick and
> powerful.

> However SQLServer thinks that [Field] = 'value' is an assignment rather
than
> an expression so it freaks out.

> Any ideas what the correct syntax is?

> Cheers


 
 
 

Aggregating Boolean Expressions - SUM(([Field] = 'value') + ([Field] = 'value'))

Post by BP Margoli » Wed, 28 Feb 2001 01:35:42


John,

select case when [Field] = 'value' then 1 else 0 end

------------------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> Hello

> This is a technique I use alot in Jet SQL because it is very quick and
> powerful.

> However SQLServer thinks that [Field] = 'value' is an assignment rather
than
> an expression so it freaks out.

> Any ideas what the correct syntax is?

> Cheers

 
 
 

1. Incrementing Next Field's Value Based Upon Prior Field's Value

MsAccess 2000
I have a continuous form bound to a table where each record contains 13 fields,
labeled a thru m.
What code can I use to increase the next field's value by one, based upon the
immediate prior field's value? My main problem is not knowing how to continue the
+1 increase from the prior record's last field to the new record's first field.

2. LATEST ON PARADOX ENGINE 3.0

3. Setting boolean field from date field values...

4. Clustered Index in SQL Server 7.0, SP2

5. combobox's listindex = database fields' value

6. INGRES QSF ERRORS (tech support is unsupportive)

7. Using Access's boolean field

8. How can I use DataCombo to access ADO data control to get over 50 rows to

9. Fact Table Fields - Don't want to aggregate/dimension

10. doesn't recognize Null value from a field value

11. Field.value doesn't show value in control

12. type field ''money''

13. How define a UDF like SUM(Field) aggregate function