Solved

# SQL variable counter for when case criteria met

Posted on 2012-08-23
438 Views
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
0
Question by:SSschultz

LVL 15

Expert Comment

Not 100% clear on what you're after.  Mayb provide some sample data and expected result.  But maybe this is what you're trying to do?

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

LVL 25

Expert Comment

do you mean?
``````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
``````
0

Author Comment

Here's some sample data.

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
0

LVL 15

Accepted Solution

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

Author Comment

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.
0

Author Comment

tim_cs

WOrks Great.. thanks very much for your help!
0

Author Closing Comment

tim_cs,

Thanks for your fast and accurate solution.
Excellent solution.
0

LVL 25

Expert Comment

It didn't suit what your later comment clarified anyway.

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.001,0)+round(C-0.001,0)+round(D-0.001,0)+round(E-0.001,0) F
FROM Table 1
0

## Featured Post

### Suggested Solutions

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This video discusses moving either the default database or any database to a new volume.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.