SELECT

@Pcounter = SUM(CASE WHEN A > .3 OR B > .4 THEN 1 ELSE 0 )

FROM

Table1

Solved

Posted on 2012-08-23

Hi,

Need help with be able to have variable counter for each row and count the criteria and have total criteria met in column.

Example.

DECLARE @Pcounter INT

SET @Pcounter =0

SELECT A,B,C,D,E

CASE WHEN A>.30 THEN @Pcounter = @Pcounter +1

WHEN B>.4 THEN @Pcounter = @Pcounter +1

ELSE @Pcounter = 0

END

FROM Table 1

Error is = , I"m not sure how to fix this for rewrite to correct.

It needs to count on each line of the table and criteria total for each line.

thanks

Need help with be able to have variable counter for each row and count the criteria and have total criteria met in column.

Example.

DECLARE @Pcounter INT

SET @Pcounter =0

SELECT A,B,C,D,E

CASE WHEN A>.30 THEN @Pcounter = @Pcounter +1

WHEN B>.4 THEN @Pcounter = @Pcounter +1

ELSE @Pcounter = 0

END

FROM Table 1

Error is = , I"m not sure how to fix this for rewrite to correct.

It needs to count on each line of the table and criteria total for each line.

thanks

8 Comments

SELECT

@Pcounter = SUM(CASE WHEN A > .3 OR B > .4 THEN 1 ELSE 0 )

FROM

Table1

```
SELECT A,B,C,D,E
@Pcounter = SUM(CASE WHEN A>.30 THEN 1
WHEN B>.4 THEN 1
ELSE 0
END)
FROM Table 1
GROUP BY A,B,C,D,E
```

A B C D E F (expected count column >.5)

car .40 .50 .60 .70 2

boat .5 .2 .8 .1 1

So column F is calculating columns B-E and counting anything greater than .5

and expect result for car would be 2 (D=.6 and E .7) and for boat 1 (D=.8)

THis is for several thousand rows table.

thanks

A,B,C,D,E,

CASE WHEN A > .5 THEN 1 ELSE 0 END + CASE WHEN B > .5 THEN 1 ELSE 0 END + CASE WHEN C > .5 THEN 1 ELSE 0 END + CASE WHEN D > .5 THEN 1 ELSE 0 END + CASE WHEN E > .5 THEN 1 ELSE 0 END AS F

FROM

Table1

I've tried your solution and get error "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

using ROUND() ... but subtracting 0.001 to ensure 0.5 rounds down ... needs to be small enough to ensure a number just over .5 doesn't get rounded down (so make it smaller that your precision)

SELECT A,B,C,D,E,

round(A-0.001,0)+round(B-0

FROM Table 1

By clicking you are agreeing to Experts Exchange's Terms of Use.

Title | # Comments | Views | Activity |
---|---|---|---|

In SQL 2008 how do I get a Pivot list of columns | 5 | 33 | |

date when the database was restored? | 6 | 46 | |

How do I get a numeric field to output to only 2 decimal points | 6 | 28 | |

SQL Date from a string | 4 | 41 |

This video discusses moving either the default database or any database to a new volume.

Join the community of 500,000 technology professionals and ask your questions.

Connect with top rated Experts

**15** Experts available now in Live!