SSschultz
asked on
SQL variable counter for when case criteria met
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
lwadwell,
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.
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.
ASKER
tim_cs
WOrks Great.. thanks very much for your help!
WOrks Great.. thanks very much for your help!
ASKER
tim_cs,
Thanks for your fast and accurate solution.
Excellent solution.
Thanks for your fast and accurate solution.
Excellent solution.
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)+ro und(C-0.00 1,0)+round (D-0.001,0 )+round(E- 0.001,0) F
FROM Table 1
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
SELECT
@Pcounter = SUM(CASE WHEN A > .3 OR B > .4 THEN 1 ELSE 0 )
FROM
Table1